Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there. In the first image below I have plotted "One Week to Date (1WTD)" sales and "Month to Date (MTD)" sales. I would like to replace MTD with "4 weeks to date (4WTD)". I would then get a consistent pattern as illustrated in image 2 (where I hand drew 4WTD).
Question:
As always, thank you.
Michelle
Solved! Go to Solution.
@michellepace
Please find below the measures that calculates One Week-To-Date and Four Week-To-Date. I used my approach for One Week To Date as it will dynamically change the week based on the current date selection range.
New Sales 1WTD =
var CurrentDate=LASTDATE(Dates[Date])
var DayOne = CALCULATE(MIN(Dates[Date]),WEEKDAY(Dates[Date],3)=0,ALLSELECTED(Dates[Date]))
var FourWeekStart = MAXX(GENERATESERIES(DayOne,CurrentDate,7),[Value])
return
CALCULATE(
[Total Sales],
FILTER(
ALLSELECTED(Dates[Date]),
Dates[Date] >= FourWeekStart && Dates[Date] <= CurrentDate)
)
New Sales 4WTD =
var CurrentDate=LASTDATE(Dates[Date])
var DayOne = CALCULATE(MIN(Dates[Date]),WEEKDAY(Dates[Date],3)=0,ALLSELECTED(Dates[Date]))
var FourWeekStart = MAXX(GENERATESERIES(DayOne,CurrentDate,28),[Value])
return
CALCULATE(
[Total Sales],
FILTER(
ALLSELECTED(Dates[Date]),
Dates[Date] >= FourWeekStart && Dates[Date] <= CurrentDate)
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@michellepace , please refer my blog on WTD/week
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...
@michellepace
Please find below the measures that calculates One Week-To-Date and Four Week-To-Date. I used my approach for One Week To Date as it will dynamically change the week based on the current date selection range.
New Sales 1WTD =
var CurrentDate=LASTDATE(Dates[Date])
var DayOne = CALCULATE(MIN(Dates[Date]),WEEKDAY(Dates[Date],3)=0,ALLSELECTED(Dates[Date]))
var FourWeekStart = MAXX(GENERATESERIES(DayOne,CurrentDate,7),[Value])
return
CALCULATE(
[Total Sales],
FILTER(
ALLSELECTED(Dates[Date]),
Dates[Date] >= FourWeekStart && Dates[Date] <= CurrentDate)
)
New Sales 4WTD =
var CurrentDate=LASTDATE(Dates[Date])
var DayOne = CALCULATE(MIN(Dates[Date]),WEEKDAY(Dates[Date],3)=0,ALLSELECTED(Dates[Date]))
var FourWeekStart = MAXX(GENERATESERIES(DayOne,CurrentDate,28),[Value])
return
CALCULATE(
[Total Sales],
FILTER(
ALLSELECTED(Dates[Date]),
Dates[Date] >= FourWeekStart && Dates[Date] <= CurrentDate)
)
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hello @Fowmy .
You've done it again, thank you so very much! I have uploaded the solution file here, in case it is useful to anyone else. This is what it looks like:
It's going to take me a little while to pick through your equations to understand and learn from them. Is there a way I can "step-through" them and see the values of the variables as the equation evaluates? Would I need to use something like Dax Studio to do this? (I've only heard of it, never used it)
@michellepace
Glad it worked for!
Follow these links to learn how to step through DAX codes and even debug them. I mostly use CONCATENATEX and COMBINEVALUES.
https://www.youtube.com/watch?v=9SV2VnYbgg4
https://radacad.com/some-simple-ways-to-debug-your-dax-measure-code-in-power-bi-debugging-virtual-ta...
Good Luck
Fowmy
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
For anyone interested - the youtube video Fowny recommends above (10 minutes from guy in a cube) is EXCELLENT.
@michellepace - First, you probably want something like Rolling Weeks - https://community.powerbi.com/t5/Quick-Measures-Gallery/Rolling-Weeks/m-p/391694#M128
Also, you might find Sequential helpful - https://community.powerbi.com/t5/Quick-Measures-Gallery/Sequential/m-p/380231#M116
Also, you may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000
Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
25 | |
19 | |
16 | |
8 |
User | Count |
---|---|
59 | |
50 | |
44 | |
21 | |
19 |