# DAX last value of the financial year

• ### 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 Sunday, August 19, 2018 7:25 AM
Saturday, August 18, 2018 1:51 PM

• Hi James,

>>>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,

>>>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,

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,