Fiscal YTD with date/Month from Table

DAX DAX Calculations
1

I need to calculate my Fiscal Year Today Values.
If I hard Code the date in the formula, It works fine.

Test March Financial year Today =
CALCULATE(
[Total Sales] ,
DATESYTD( Dates[Date], “31/03” ) )

Since The End of the Financial year Depends on which company I am working on, I need to Dynamically adjust the " 31/03" to Measure since I have the end of the financial year stored in a table GeneralLedgerManager

The error is around " Only Constant DAte VAlue is Allowed as Year-end Argument"

Foro YTD

Any suggestion on how to solve this situation?
Attached is PBIX with Example

Thanks
Foro YTD_.pbix (893.2 KB)

2

Hi @ezenunez
Yes I’ve been here too. Shame you cannot set a the “31/03” as a parameter somewhere an let TOTALYTD work its magic.

You need to set your date table up so that it manages fiscal years, then filter on the fiscal year in question. EDNA have an extended date table here.
Extended Date Table (Power Query M function).

I had already written mine in excel rather than power query before I saw the EDNA one which is just fantastic. So if I use my calendar the DAX below should make sense.
BINavigationCalendar.xlsx (6.6 MB).
If you put 31/03/2021 in the KeyDates tab FinancialYearEnd, the calendar will work the necessary through. The year doesn’t matter since the logic in the calendar derivation just looks at month number.

Then you need a piece of DAX like this:

VAR YTDDates = DATESBETWEEN('Calendar'[Date],max( 'Calendar'[Fisc YearStart] ),max( 'Calendar'[Date] )) 
Return 
CALCULATE( [YourMeasure], YTDDates )

The logic is to attack the date table and filter between Fiscal Year start and currently selected date. Fiscal Year start will show (assuming March fiscal year end) 01/04/YYYY in every date row for the fiscal year. Max(calendar[Date]) is the currently selected date.

I would recommend tweaking this with the EDNA calendar should you use it. This is because the EDNA calendar generates directly in PQ so no data upload.

Hope this helps

Pete

3

Thanks Pete,
I was also expecting a solution more in the line of a parameter!
I do have my calendar year with my fiscal year columns. I need to work out my Dax formulas.
Thanks a lot for your help.
Have a good day.
Cheers,
Ezequiel

4

Hi @ezenunez what i found(DAX PATTERNS BOOK second Edition) is that you can not set it at “31/03” dynamically and I quote from the book page 37 :“The Sales Fiscal YTD measure specifies the last day and month of
the fiscal year in the second argument of DATESYTD. The following
measure uses June 30 as the last day of the fiscal year. The second
argument of DATESYTD must be a constant value (also called a
literal) corresponding to the definition of the fiscal year in the Date
table; it cannot be computed dynamically”

5

Thanks for looking at this!

Do you recommend DAX PATTERNS BOOK ?

Cheers

6

of course if you did get DAX basics you can find it contents in the web site for free without buying the book(sqlbi.com)