cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
duggy Helper III
Helper III

Comparing same day of week last year

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?

4 REPLIES 4
judspud Solution Supplier
Solution Supplier

Re: Comparing same day of week last year

Hi @duggy,

 

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

2019-03-29_1118.png

Hope this helps.

 

Thanks,

George

duggy Helper III
Helper III

Re: Comparing same day of week last year

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?

Highlighted
judspud Solution Supplier
Solution Supplier

Re: Comparing same day of week last year

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 Helper III
Helper III

Re: Comparing same day of week last year

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:

Capture.JPG

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


 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors