How do you compare DAY of Week - Week over Week?

[whatisdata96]

Hi all,

I am looking for a way to be able to compare each day of week separately week over week. For example Thursday 9/8, Thursday 9/15, Thursday 9/22, etc. I want to be able to get show totals for this.

I am working with call center data and I'd like to analyze call drivers based off of the day of week to understand where things are trending.

I have a date table that contains, Date (obviously), month, year, month num, Day of week, Day of week num, etc etc. Basically any date dimension you'd usually have, I have in my model.

I've never had an ask to seperatly compare the progression of the same day of week. Has anyone done this before? How would I even do this? I'm having a hard time wrapping my head around this.

[speedramps]

You can also do this ....

Create calendar table with 1:M relationship to your fact tabels dates

Learn how here Calendar Table training

Then  add a day name column (not measure) to your calandar table

Day name = FORMAT('Calendar'[Date],"ddd")

Then create this measure (not a column) 

Total sales =  SUM(Sales[Amount])

And plot a line graph by month by DAY name

or a line graph by week by DAY name

or a line graph by quater by DAY name

all these options will allow to see the trend and easily spot any outliers  

Thanks for reaching out for help.

I put in a lot of effort to help you, now please quickly help me by giving kudos.

Remember we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. So please click the thumbs up and accept as solution button. 

If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime.  I prefer to teach members on this forum techniques rather give full solutions and do their job. You can then adapt the technique for your solution, learn some DAX skills for next time and soon become a Power BI Super User like me. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

[v-zhangti]

Hi, @whatisdata96 

Add a new column of weeks to the date table.

Column:

Weekday = WEEKDAY([Date],2)

Sample data:

The comparison of Thursdays can be obtained through the slicer.

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[whatisdata96]

Okay so I'm thinking I may have not explained this correctly.

I don't want to get totals for that week.

I want to compare this Monday to last Monday. Meaning, I don't care about the totals for the whole week. I just want to figure out that specific day of week. I'd like to do this for all days of week.

I could just use a date column and manually filter the dates to what I want them to be, but that's really not what I want.

I'd like to be able to automagically have a comparison visualization for all days of week individually.

[speedramps]

Create a calandat table with:-

• date
• day of week (mon, tue, wed, thurs, fri, sat, sun)
• week commencing date

Build a relationship  calendar[date] 1:M to  calls[date]

Create a measure

Total calls = SUM(calls[amount])

Draw a line graph for Total calls by week commencing date.

Note this will show the total sales per week.

Then comes the clever bit ....

add a single slection slicer for day of week

If you slice Thursday then the line graph will show the trend for Thusday,

If you slice Sunday then the line graph will show the trend for Sunday. 😀😀😀

Thanks for reaching out for help.

I put in a lot of effort to help you, now please quickly help me by giving kudos.

Remember we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. So please click the thumbs up and accept as solution button. 

If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime.  I prefer to teach members on this forum techniques rather give full solutions and do their job. You can then adapt the technique for your solution, learn some DAX skills for next time and soon become a Power BI Super User like me. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

[whatisdata96]

Okay so I'm thinking I may have not explained this correctly.

I don't want to get totals for that week.

I want to compare this Monday to last Monday. Meaning, I don't care about the totals for the whole week. I just want to figure out that specific day of week. I'd like to do this for all days of week.

I could just use a date column and manually filter the dates to what I want them to be, but that's really not what I want.

I'd like to be able to automagically have a comparison visualization for all days of week individually.

[speedramps]

Hi whatisdata96

Okay, actually you have explained this correctly ... but you not understood how to use  the 2 good solutions offered by solvers 😀😀😀

Let me try explain better ...

Create calendar table with 1:M relationship to your fact tabels dates

Learn how here Calendar Table training

Then  add a day name column (not measure) to your calandar table

Day name = FORMAT('Calendar'[Date],"ddd")

Then creates measure (not columns) for each day

Mon sales = CALCULATE([Total sales], 'Calendar'[Day name] = "Mon")

Tue sales = CALCULATE([Total sales], 'Calendar'[Day name] = "Tue")

Wed sales = CALCULATE([Total sales], 'Calendar'[Day name] = "Wed")

etc

and plots them on graph