Forum Discussion

PhilipD's avatar
PhilipD
New Member
4 years ago

YTD comparison with data from a fixed year

Dear experts,
I cannot get my head around the following requirement for one of my reports that presents the number of orders (we're an escape room operator) over time.

 

Basic function of that report (working properly):

  • display the orders placed during the period of time that can be selected by a slicer/date filter
  • display last years orders up to the currently selected end date of the period under consideration (I used sameperiodlastyear)

 

I would like to add a comparison to the orders that have been placed in 2019 since that was the last "normal", pre-Corona year that still serves as basis for budget consideration, assumed to be a "normal" year. So if my slicer is set to 1.1.2022 - 26.01.2022, I would like to see:

  • no. of orders 1.1.2022 - 26.01.2022
  • no. of orders 1.1.2021 - 26.01.2022 (LY - not very relevant in Corona times)
  • no. of orders 1.1.2019 - 26.01.2019 (more relevant)

 

However, if I set my slicer to 1.7.2020 - 31.7.2020 (for whatever reasons), I would like to see:

  • no. of orders 1.7.2020 - 31.7.2020
  • no. of orders 1.7.2019 - 31.7.2019 (LY)
  • no. of orders 1.7.2019 - 31.7.2019 (fixed 2019-comparison)

I tried to find an answer to that question. Please excuse my lack of PowerBI/DAX knowledge if the answer is already out there but I didn't manage to ask the question correctly so that I would easily find the solution.

Thank you very much in advance for your support!

Need Help

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    The usual approach is to add a couple of flags to your Calendar table, like 

     

    IsPastPY  - if a date is on or before today's date but for last year

    IsPastPUY - same but for the penultimate year

     

    Now assuming that your Calendar table is in import mode or calculated in DAX, and that you refresh your dataset daily,  you can use these flags to compute the "prior year to date"  and "penultimate year to date"  values.  For Prior year you can use the built in DAX function:

     

    Prior Year total to Date = CALCULATE (<value>,SAMEPERIODLASTYEAR(<date>),IsPastPY=TRUE())

     

    For the penultimate year the DAX is a little more complex, but the idea is the same.  I have also seen people use SAMEPERIODLASTYEAR(SAMEPERIODLASTYEAR(<date>)) but that's just sneaky.

     

  • PhilipD's avatar
    PhilipD
    New Member
    4 years ago

    Thank you for your reply, lbendlin !

    Your solution works if I set my date slicer to the present year (2022) which is ok for the use case at hand. However, if I wanted to be able to change the "present date" (by adjusting the slicer to e.g. 1st of January 2021), the calculated dates are moved back similarly (in this case I would see 2018 instead of 2019 figures), won't they?