locked
DAX last value of the financial year RRS feed

  • Question

  • I'm trying to get the value (GBPVALUE) of the first transaction in the current financial year.

    DATE_REC contains the date of the transaction, our financial year ends on March 31st.

    Can anyone point out where I've gone wrong?

    =calculate(sum(Table1[GBPVALUE]),filter(all(Table1[DATE_REC]),ENDOFYEAR(Table1[DATE_REC],"3/31")))

    Thanks, James


    • Edited by JHD13 Sunday, August 19, 2018 7:25 AM
    Saturday, August 18, 2018 1:51 PM

Answers

  • Hi James,

    Thanks for your question.

    >>>I'm trying to get the value (GBPVALUE) of the first transaction in the current financial year.

    If I understand you correctly, you might try below DAX formula:

    =
    CALCULATE (
        SUM ( Table1[GBPVALUE] ),
        FILTER (
            ALL ( Table1[DATE_REC] ),
            Table1[DATE_REC] = NEXTDAY ( ENDOFYEAR ( Table1[DATE_REC], "3/31" ) )
        )
    )
    


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, August 20, 2018 1:42 AM
  • Thanks. I managed to get it to work with this:

    =

    var FIRSTSALE=calculate(min('Table1'[DATE_REC]),ALL('Calendar'[FiscalMonth],'Calendar'[Month],'Calendar'[Month Number]),all(Table1[SUPPLIER_GROUP]))


    return

    if

    (Table1[Sum of UNITS_REC]>0,


    CALCULATE

    ([GBPAVGPRICE],ALLSELECTED(Table1),Table1[DATE_REC]=FIRSTSALE),


    blank

    ())

    Tuesday, August 21, 2018 10:18 PM

All replies

  • Hi James,

    Thanks for your question.

    >>>I'm trying to get the value (GBPVALUE) of the first transaction in the current financial year.

    If I understand you correctly, you might try below DAX formula:

    =
    CALCULATE (
        SUM ( Table1[GBPVALUE] ),
        FILTER (
            ALL ( Table1[DATE_REC] ),
            Table1[DATE_REC] = NEXTDAY ( ENDOFYEAR ( Table1[DATE_REC], "3/31" ) )
        )
    )
    


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, August 20, 2018 1:42 AM
  • Thanks Willson, how would I find the first value of the year?

    Sometimes, there are no sales on the first day.

    I presume I change ENDOFYEAR to STARTOFYEAR but what else needs to change?

    Thanks, James

    Monday, August 20, 2018 4:26 PM
  • Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    With PQ generated Financial Year/Month/Quarter/Day Date Table.
    No Time Intelligence Functions.
    Find first/last sales in financial years.
    http://www.mediafire.com/file/5nb9k34fi68a9i8/08_20_18.xlsx/file
    http://www.mediafire.com/file/qlhcw3ouwb1dvwg/08_20_18.pdf/file

    Tuesday, August 21, 2018 1:36 AM
  • Hi James,

    Thanks for your response.

    In this scenario, you might want to try DAX function firstnonblank. For more detailed information, please refer to below blog:
    https://www.sqlbi.com/articles/alternative-use-of-firstnonblank-and-lastnonblank/


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, August 21, 2018 4:54 AM
  • Thanks. I managed to get it to work with this:

    =

    var FIRSTSALE=calculate(min('Table1'[DATE_REC]),ALL('Calendar'[FiscalMonth],'Calendar'[Month],'Calendar'[Month Number]),all(Table1[SUPPLIER_GROUP]))


    return

    if

    (Table1[Sum of UNITS_REC]>0,


    CALCULATE

    ([GBPAVGPRICE],ALLSELECTED(Table1),Table1[DATE_REC]=FIRSTSALE),


    blank

    ())

    Tuesday, August 21, 2018 10:18 PM
  • Hi James,

    Thanks for your response.

    I am glad to know that you have got it to work. Please kindly mark the helpful replies as answers. By doing so, it will benefit all community members who are facing this similar issue. Your contribution is highly appreciated.


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, August 22, 2018 12:52 AM