topic Re: Dax formula for "4 Weeks to Date" (I have 1WTD) in DAX Commands and Tips

Dax formula for "4 Weeks to Date" (I have 1WTD)

michellepace — Mon, 31 Aug 2020 07:05:38 GMT

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:

Could someone please help me with the DAX code for "4WTD" similar to my 1WTD formula?
Dataset & .pbix: here (dataset from Enterprise DNA's Ultimate Beginners Guide to Dax)
1WTD formula: (formula from RADCAD's tutorial )

As always, thank you.
Michelle

Re: Dax formula for "4 Weeks to Date" (I have 1WTD)

Greg_Deckler — Sat, 29 Aug 2020 18:38:05 GMT

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-TITHW/m-p/434008

Re: Dax formula for "4 Weeks to Date" (I have 1WTD)

Fowmy — Sun, 30 Aug 2020 07:26:50 GMT

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 🙂

YouTube LinkedIn

Re: Dax formula for "4 Weeks to Date" (I have 1WTD)

amitchandak — Mon, 31 Aug 2020 06:36:23 GMT

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-Last-Week/ba-p/1051123

Re: Dax formula for "4 Weeks to Date" (I have 1WTD)

michellepace — Mon, 31 Aug 2020 07:38:24 GMT

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)

Re: Dax formula for "4 Weeks to Date" (I have 1WTD)

Fowmy — Mon, 31 Aug 2020 07:07:19 GMT

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-tables

Good Luck
Fowmy

Re: Dax formula for "4 Weeks to Date" (I have 1WTD)

michellepace — Wed, 02 Sep 2020 15:17:41 GMT

For anyone interested - the youtube video Fowny recommends above (10 minutes from guy in a cube) is EXCELLENT.