Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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 :
Solved! Go to 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 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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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
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.
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. 😊
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
Thanks for the prompt response @parry2k ! Appreciate it. Will share the pbix file soon.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
145 | |
79 | |
64 | |
52 | |
47 |
User | Count |
---|---|
216 | |
89 | |
76 | |
67 | |
60 |