Comparing same day of week last year

[duggy]

Hi,

I have a report due that needs to compare the same day of week this year to that of last year. So where before I would compare 1 March 2019 to 1 March 2018, I now need to compare the same day of week last year.

So if 1 March 2019 was a Friday, then I need to compare it to the previous years 2 MArch which would be the friday. Does that make sense? The activity is not necessarily based on day of the month, but on day of the week in the month, so the first Monday of the month this year compared to the first Monday of the month last year etc.

I wouldnt have a clue how to even begin this....

Amy solutions?

[judspud]

Hi @duggy,

You can simply subtract 364 from the date in question. The below measures were created using Today() and Today()-364.

Hope this helps.

Thanks,

George

[duggy]

This would give me the same date last year, not the same day of the week last year.

1 March 2019 was a Friday, 1 MArch 2018 was a saturday (for example). I cant compare activity from a friday to a saturday. I need to compare this 1 MArch 2019 (Friday) to the year before that Friday. So if the year before that was a saturday, then I need to go back to the first Friday before 1 March 2018. Make sense?

[judspud]

Subtracting 365 days would give the same date.

In your example subtracting 364 days from friday 1st march 2019 gives friday 2nd march 2018.

You could build in logic to account for leap years and to check that the date is in fact a friday.

Summary of logic would be;

if( date-364 = friday ,date-364 ,if( date-364 = saturday, date-365, etc...

hope this makes sense

Thanks,

George

[duggy]

Is there a way to automate this?

See image below. This is how we work out the figure for he previous year (same date), next to that column we need the figure for the same day last year as stated above:

This is how we get the value for the previous year same date:

2018 = 
VAR maxDate =
    MAX ( ‘Visitors[Date] )
VAR storeid =
    MAX ( ‘Visitors[StoreId] )
VAR minDate =  MIN ( ‘Visitors[Date] )
RETURN
    CALCULATE (
        SUM ( ‘Visitors[Visitors] ),
        FILTER (
            ALL ( ‘Visitors ),
            ‘Visitors[StoreId] = storeid && ‘Visitors[Date] >= DATE (year(minDate)-1,  month(minDate) , day(minDate)  )
                && ‘Visitors[Date]
                    <= DATE ( YEAR ( maxDate )-1 , MONTH ( maxDate ), DAY ( maxDate ) )
        )
    )