Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Previous Year DAX

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('D...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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))
     
     
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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's avatar
v-jingzhang
Community Support
5 years ago

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.