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('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 🙂
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))
     
     

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper 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
        Icon for Community Support rankCommunity 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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    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))