Forum Discussion

Naveeduddin's avatar
Naveeduddin
Frequent Visitor
4 years ago
Solved

YoY Date Comparison

Hi All,

 

I'm trying to do a YoY Comparison for 2021 and 2020, so when I use the sameperiodlastyear it compares the exact same date last year but I want it to calculate one day ahead

 

For Ex: My date range is 19 Dec 2021 to 25 Dec 2021, it should compare 20 Dec 2020 to 26 Dec 2020

 

Kindly help!

  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    All measures are in the attached pbix file.

    If I may suggest other than the below, if you have calendar table with week number column, you can compare it by utilizing the same week number last year.

     

    Sales: =
    SUM(Data[Sales])

     

     

    Sales previous year expected result: =
    VAR mindate =
    CALCULATE ( MIN ( 'Calendar'[Date] ), SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) ) + 1
    VAR maxdate =
    CALCULATE ( MAX ( 'Calendar'[Date] ), SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) ) + 1
    RETURN
    CALCULATE ( [Sales:], DATESBETWEEN ( 'Calendar'[Date], mindate, maxdate ) )

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Naveeduddin , Try a measure like

    Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,DAY))

  • bcdobbs's avatar
    bcdobbs
    Community Champion
    4 years ago

    Not by a computer however SAMEPERIODLASTYEAR returns a table of dates. I think you should just be able to wrap it in DATEADD.

     

    Can flesh that out later if you need?

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    4 years ago

    Hi,

    Please check the below picture and the attached pbix file.

    All measures are in the attached pbix file.

    If I may suggest other than the below, if you have calendar table with week number column, you can compare it by utilizing the same week number last year.

     

    Sales: =
    SUM(Data[Sales])

     

     

    Sales previous year expected result: =
    VAR mindate =
    CALCULATE ( MIN ( 'Calendar'[Date] ), SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) ) + 1
    VAR maxdate =
    CALCULATE ( MAX ( 'Calendar'[Date] ), SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) ) + 1
    RETURN
    CALCULATE ( [Sales:], DATESBETWEEN ( 'Calendar'[Date], mindate, maxdate ) )
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I was trying to get there. But you did faster! Jihwan_Kim