Forum Discussion

sarah2_williams's avatar
sarah2_williams
Icon for Helper III rankHelper III
6 years ago
Solved

YoY Comparison totals - DAX Help

Hi all,   I am trying to do a year on year comparison on the number of deposits paid for courses starting in September. I have tried using the SAMEPERIODLASTYEAR function but that only gived me the...
Need Help
  • sarah2_williams's avatar
    sarah2_williams
    6 years ago

    Thanks, now I can see what you mean by showing this cumulative year. how can I rectify this in the attached image.

    In its test file, the Standalone Card has the Accumulated Measure of the previous year, which shows the correct amount 982, this is correct to note that the maximum on date is 07/08/2020, and this is the total for the academic year 2019 through 07/08/2019. I copied the DAX exaclty but my stll card shows the wrong number.

    Capture.JPG

    I can't see why it doesn't work. Maybe I'll have to try something else.

    Thank you

richbenmintz's avatar
richbenmintz
Icon for Resident Rockstar rankResident Rockstar
6 years ago

Hi sarah2_williams,

 

Please provide a sample table of data and the expected results, much easier to craft a measure that works if provided.

 

Thanks,

Richard

sarah2_williams's avatar
sarah2_williams
Icon for Helper III rankHelper III
6 years ago
Created DateDeposit PaidAcademic Year
01/11/2018119/20
01/12/2018019/20
01/01/2019119/20
01/02/2019119/20
01/03/2019119/20
01/04/2019019/20
01/05/2019119/20
01/06/2019019/20
01/07/2019119/20
01/08/2019019/20
07/08/2019119/20
01/09/2019119/20
01/10/2019119/20
01/11/2019020/21
01/12/2019020/21
01/01/2020020/21
01/02/2020020/21
01/03/2020120/21
01/04/2020020/21
01/05/2020120/21
01/06/2020120/21
01/07/2020020/21
01/08/2020020/21
07/08/2020120/21

 

From the above I would like to show that for 20/21 Academic Year 4 deposits have been paid up until 7th August 2020, and this would then be compared to the 7 deposits paid for the 19/20 academic year by the 7th August 2019, the data updates weekly on a Friday, so I need it to look for the latest date this year, to then hopefully use that date last year. The end goal is to then create the % change YoY which I am able to do once the 2 seperate totals are worked out.

 

Thanks all for the help

S

  • richbenmintz's avatar
    richbenmintz
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    Hi sarah2_williams,

     

    I have created a couple Helper Columns in your Dataset

    Year and DateInt, makes the prior period calcs really simple, best practice would be to create a date table and join to your fact table, but given that you are dealing with a non standard calendar, I would likely add these columns to your date table and use the same calcs, just referencing the date table rather than the fact table

     

    Current Year Cummulative Deposits = 
    CALCULATE(sum('Deposits'[Deposit Paid]), filter(ALL(Deposits), 'Deposits'[AcademicYearNumber] = max(Deposits[AcademicYearNumber]) && 'Deposits'[DateInt] <= MAX('Deposits'[DateInt])))

Prior Year Cummulative Deposits =  
    CALCULATE(sum('Deposits'[Deposit Paid]), filter(ALL(Deposits), 'Deposits'[AcademicYearNumber] = max(Deposits[AcademicYearNumber])-1 && 'Deposits'[DateInt] <= max('Deposits'[DateInt]) - 10000))

     

    Data I used looks like this

    Created DateDeposit PaidAcademic YearAcademicYearNumberDateInt

    November 1, 2018119/20201920181101
    December 1, 2018019/20201920181201
    January 1, 2019119/20201920190101
    February 1, 2019119/20201920190201
    March 1, 2019119/20201920190301
    April 4, 2019019/20201920190404
    May 1, 2019119/20201920190501
    June 1, 2019019/20201920190601
    July 1, 2019119/20201920190701
    August 1, 2019019/20201920190801
    August 7, 2019119/20201920190807
    September 1, 2019119/20201920190901
    October 1, 2019119/20201920191001
    November 1, 2019020/21202020191101
    December 1, 2019020/21202020191201
    January 1, 2020020/21202020200101
    February 1, 2020020/21202020200201
    March 1, 2020120/21202020200301
    April 1, 2020020/21202020200401
    May 1, 2020120/21202020200501
    June 1, 2020120/21202020200601
    July 1, 2020020/21202020200701
    August 1, 2020020/21202020200801
    August 7, 2020120/21202020200807

     

    Hope this helps,

    Richard 


    Did I answer your question? Mark my post as a solution!
    Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

    • sarah2_williams's avatar
      sarah2_williams
      Icon for Helper III rankHelper III
      6 years ago

      Hi,

       

      The current year cululative worked for me.. but the last year one did not.

       

      This is what I used. The error on prior year is "The MAX function only accpepts a colum reference as an argument... but I did not get this error on current year, same colums are used in both.

       

      Current Year Cummulative Deposits =
          CALCULATE(sum('ALL APPS'[Deposit Paid]), filter(ALL('ALL APPS'), 'ALL APPS'[AcademicYearNumber] = max('ALL APPS'[AcademicYearNumber]) && 'ALL APPS'[Created Date] <= MAX('ALL APPS'[Created Date])))

       

      Prior Year Cummulative Deposits =
          CALCULATE(sum('ALL APPS'[Deposit Paid]), filter(ALL('ALL APPS'), 'ALL APPS'[AcademicYearNumber] = max('ALL APPS'[AcademicYearNumber]-1 && 'ALL APPS'[Created Date] <= MAX('ALL APPS'[Created Date]) - 10000)))

       

      Any further help would be appreciated.

       

      Thanks

      Sarah

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi sarah2_williams ,

         

         

        Prior Year Cummulative Deposits =
CALCULATE (
    SUM ( 'ALL APPS'[Deposit Paid] ),
    FILTER (
        ALL ( 'ALL APPS' ),
        'ALL APPS'[AcademicYearNumber]
            = MAX ( 'ALL APPS'[AcademicYearNumber] ) - 1
            && 'ALL APPS'[Created Date]
                <= MAX ( 'ALL APPS'[Created Date] ) - 10000
    )
)

         

        You missed a ) after MAX function

         

        Regards,

        Harsh Nathani 

    • sarah2_williams's avatar
      sarah2_williams
      Icon for Helper III rankHelper III
      6 years ago

      Thanks

       

      I now have values but for the prior year they are all out by about 20.. can you explain to me how the prior year formula identifys the date it should look for in the previous year? and why are we using "-10000". Thanks again for the help.. just trying to make sure I understand properly 🙂

      S

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi sarah2_williams ,

         

        Use solution from v-lili6-msft . 

         

        It makes use of Time Intelligence which is needed in your case.

         

        Regards,

        Harsh Nathani