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

[michellepace]

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

[amitchandak]

@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...

[Fowmy · Accepted 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 🙂

YouTube  LinkedIn

[michellepace]

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)

[Fowmy]

@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

[michellepace]

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

[Greg_Deckler]

@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...