Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Compare Date Range with Previous Year Equivalent:

Hi everyone!

 

I have a slicer where I can select a range of dates and I need to compare with the equivalent days of the previous year selected.

Example:
If I select the date range from 10/20/2020 (Tuesday) to 10/28/2020 (Wednesday) it should compare with the values from 10/22/2019 (Tuesday) to 10/30/2019 (Wednesday).

 

Currently I have a measure that works but it compares me only one day:

CALCULATE (
    SELECTEDMEASURE (),
    FILTER (
        ALL ( 'Dim_Date' ),
        'Dim_Date'[Year]
            = MAX ( 'Dim_Date'[Year] ) - 1
            && 'Dim_Date'[Week of Year]
                = MAX ( 'Dim_Date'[Week of Year] )
            && 'Dim_Date'[Day of Week]
                = MAX ( 'Dim_Date'[Day of Week] )
    )
)

 

Thanks !

Regards!

Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , I think you are looking for data 364 days behind

     

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

6 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    5 years ago

    THIS post is not helpful, as I did not read the question properly, I'm sorry for the confusion.

    Hey Anonymous ,

     

    I'm wondering why you are not using the DAX function

    SAMEPERIODLASTYEAR (https://dax.guide/sameperiodlastyear/#)

     

    Maybe I miss something.

     

    Regards,

    Tom

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi TomMartens,

       

      I don't use the SAMEPERIODLASTYEAR function because it compares me to the same days, for example 10/20/2020 to 10/28/2020 vs 10/20/2019 to 10/28/2019 but I don't need that.

      In my case, I need to be equivalent days as I mentioned in the example.

      Thanks!

      • ibarrau's avatar
        ibarrau
        Icon for Super User rankSuper User
        5 years ago

        Hi, if you have your date table you might be able to use time intelligence. The function you need is 

        EDIT: I have just seen the 2019. It's -363 or -364 days to take it.

         

        DATEADD(Date[Datecolumn], -364, DAY)

         

         

        You can build a measure with CALCULATE and that as filter expresion to get the following 2 days for each date in the range. You will be able to compare it with that filter expresion.

        Hope that helps 

  • tex628's avatar
    tex628
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Try this,

    CALCULATE (
    SELECTEDMEASURE (),
    FILTER (
        ALL ( 'Dim_Date' ),
               'Dim_Date'[Year]
                <= MAX ( 'Dim_Date'[Year] ) - 1
            && 'Dim_Date'[Week of Year]
                <= MAX ( 'Dim_Date'[Week of Year] )
            && 'Dim_Date'[Day of Week]
                <= MAX ( 'Dim_Date'[Day of Week] )
            && 'Dim_Date'[Year]
                >= MIN ( 'Dim_Date'[Year] ) - 1
            && 'Dim_Date'[Week of Year]
                >= MIN ( 'Dim_Date'[Week of Year] )
            && 'Dim_Date'[Day of Week]
                >= MIN ( 'Dim_Date'[Day of Week] )
    )
)