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))
     
     
amitchandak's avatar
amitchandak
Super User
5 years ago

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'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.