cancel
Showing results for
Did you mean:
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
Highlighted
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.

Hope this helps.

Thanks,

George

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?

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

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:

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

```

Announcements

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!

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

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.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors