DAX showing the last 5 values w/ the same day of week (e.g. Tues only) as my from selcted week nmbr

[Anonymous]

I'm currently doing a daily dashboard with a different sales value per day. So basically I want to create a matrix visual only showing the sales for the same day the past 5 weeks. For example, I will select week 23 and I selected  Monday, I want the visual to show only Monday, last Monday, Monday weeks ago, Monday 3 weeks ago in a Matrix. And I want this to be dynamic...so i select Tuesday, then Tuesday data from the past 5 weeks should show, then Wednesday, only Wednesday and so on. 

Is there any DAX formula that I could use to do this? Thank you!!! I've been trying to go this direction but have been failing :

Thank you.

[Ritaf1983 · Accepted solution]

Hi @Anonymous 
You have to create a calendar table with the needed columns (weekday, week number)

The week number and day can be used in slicers :

Then you can create measures for the week's sales with wanted offsets:

current week sales = [total_sales]

last week salaes = CALCULATE([curent week sales],DATEADD('Calendar'[Date],-7,DAY))

2 weeks ago sales = CALCULATE([Total sales (K/$)],DATEADD('Calendar'[Date],-14,DAY))

.

.

.

To make the choice which measures to show you can use a field parameters :

pbix with an example is attached.

More guides about creating calendar tables is by the link:

https://radacad.com/do-you-need-a-date-dimension

About field parameters :

https://www.youtube.com/watch?v=NVREYTHxBvU

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