DAX showing the last 4 values w/ the same day of week (e.g. Tues only) as my latest day's value

[chocochoco]

Hi everyone, 

I'm currently doing a daily dashboard with a different sales value per day. So basically I want to create a bar visual only showing the sales for the same day the past 4 weeks. For example, today is Monday, I want the visual to show only today, last Monday, Monday 2 weeks ago, Monday 3 weeks ago in a bar. And I want this to be dynamic...so if the next day is a Tuesday, then Tuesday data from the past 4 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 :

Same DayOfWeek Sales = calculate([Total Sales],

(WEEKDAY([Max Date],2)=WEEKDAY([Total Sales])

My logic is : I need to calculate total sales but filtered only to those dates where the weekday (1 to 7) is the same as the max date's weekday. (Plus something to add so I'd only look at the past 4 weeks' data)

I'm a huge beginner at this - only started weeks ago after a few YouTube videos) - so would really appreciate the help! Thank you.

[parry2k]

@chocochoco although @v-kkf-msft solution will work I would prefer to use the Time Intelligence function. Make your calendar date table as date table and use the following measure

Measure 2 = 
var Today = 
    MAXX( 
        ALL(Volume_0521), 
        Volume_0521[PUP Date] 
    )
VAR LastHowManyWeeks = 5 --including current
Return 
    CALCULATE(
        [Total Sales],         
        KEEPFILTERS ( DATESINPERIOD ( 'Calendar Date Table'[Date], Today, - (LastHowManyWeeks*7 ), DAY ) ),
        'Calendar Date Table'[DayOfWeek] = WEEKDAY( Today, 2 )
    )
    

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[chocochoco]

Thanks @parry2k and @v-kkf-msft ! Both solutions work, however when I try to pick an older date (for example, a date before the last date of my table), the visual returns a blank. Would you know how to solve this issue? Thanks again

[v-kkf-msft · Accepted solution]

Hi @chocochoco ,

This is because a relationship has been created between the Calendar Date Table and the Volume_0521 table. When you select "5/30/2021" in the slicer, the rows with the "5/30/2021" PUP Date in Volume_0521 table will be filtered out. Then the measure will be calculated for these rows, and the result will return blank value.

If you want to dynamically change according to the date selected in the slicer, you need to delete the relationship between the two tables.

Then use the following formula:

Measure = 
var SelectDate = MAX('Calendar Date Table'[Date])
Return 
    CALCULATE(
        [Total Sales], 
        FILTER(
            'Volume_0521', 
            'Volume_0521'[PUP Date] >= SelectDate - 28
                && WEEKDAY('Volume_0521'[PUP Date], 2) = WEEKDAY( SelectDate, 2 )
        )
    )

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[chocochoco]

Thanks a lot! I think I kind of get it now. I'm still studying the file, but I think my understanding's going to the right direction. 😊

[parry2k]

@chocochoco This is a bit more involved than a simple DAX measure. If you can share pbix file using one drive/google drive, remove sensitive information before sharing, I will put together a solution for you.

[chocochoco]

PBIX 

Hi @parry2k here's a simple sample of what I intend to do. Hope it's enough!

[v-kkf-msft]

Hi @chocochoco ,

Try the following formula:

Measure = 
var Today = 
    MAXX( 
        ALL(Volume_0521), 
        Volume_0521[PUP Date] 
    )
Return 
    CALCULATE(
        [Total Sales], 
        FILTER(
            'Calendar Date Table', 
            'Calendar Date Table'[Date] >= Today - 28
                && 'Calendar Date Table'[DayOfWeek] = WEEKDAY( Today, 2 )
        )
    )

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[chocochoco]

Thanks for the prompt response @parry2k ! Appreciate it. Will share the pbix file soon.