Compare Date Range with Previous Year Equivalent:

[tutuk28]

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!

[tex628]

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] )
    )
)

[TomMartens]

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

Hey @tutuk28 ,

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

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

Maybe I miss something.

Regards,

Tom

[tutuk28]

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!

[amitchandak · Accepted solution]

@tutuk28 , I think you are looking for data 364 days behind

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

[ibarrau]

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 

[tutuk28]

Hi @ibarrau !

In this case the problem is that if I select the year 2019 the difference with the year 2018 is 1 day, for example:
Tuesday 12/17/2019 the equivalent day of the previous year is Tuesday 12/18/2018.

Thanks!!