Previous Year DAX

[Anonymous]

Hi all

I am using the following DAX to calculate the value of a column for the current fiscal year (UK format 1/4/20-31/3/21)

YTDsum = FORMAT(TOTALYTD(SUM(Table[Number]),'Date'[Date], ALL('Date'), "31/3"),"#,#")

Is there a way I can adapt this to get the same result but for the previous fiscal year (UK format 1/4/19-31/3/20)

I do not want to hard code the dates in, as the dataset wil be somewhat live, so I would like it to compare like for like, for example, if the data is current as of 11th November, I would like as follows:

Existing measure produces result to 11th November, as thats all the data it has.

Prev Fiscal Year to date measure - result from (UK format) 1/4/19-11/11/19, with it changing as data comes in.

Thanks 🙂

[Anonymous · Accepted solution]

Hi @v-jingzhang 

Thanks for taking the time to respond, I can't get any of those to work correctly in my report, however I found some DAX that did that I have posted below, granted it is slightly flawed but it does the job 🙂

Prev YTD Calls = CALCULATE(SUM(MitelBICalls[Answered]),DATESBETWEEN('Date'[Date],MAX('Prev Year Date'[Date]),MAX(MitelBICalls[True Date])-366))

[amitchandak]

@Anonymous , you can move a year back. example lytd

Example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"3/31"))
LYTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"3/31"))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

Hi @amitchandak 

Thanks for taking the time to reply.

The second piece of DAX:

LYTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"3/31"))

is calculating from (UK format) 1/4/19-31/12/19, thereby creating an inaccurate comparison as the data, at least, currently stops at 11th November.

Any ideas?

Do yoi

[v-jingzhang]

Hi @Anonymous 

Try the following measures:

LYTD Sales 2 = 
VAR maxDate = MAXX(ALL('Sales'),'Sales'[Date])
RETURN
CALCULATE(SUM('Sales'[Sales Amount]),DATESYTD(DATEADD('Calendar'[Date],-1,YEAR),"3/31"),'Calendar'[Date]<=EDATE(maxDate,-12))

Or

LYTD Sales 3 = 
VAR maxDate = MAXX(ALL('Sales'),'Sales'[Date])
RETURN
IF(MAX('Calendar'[Date])<=maxDate,CALCULATE(SUM('Sales'[Sales Amount]),DATESYTD(DATEADD('Calendar'[Date],-1,YEAR),"3/31")),BLANK())

Please see the differences below. Both of them stop at 11th November, but the second one will show blank after that. You may try them depending on what you need to display in the report.

Best Regards,

Community Support Team _ Jing Zhang

If this post helps, please consider Accept it as the solution to help other members find it.