topic Re: WTD for current Week in DAX Commands and Tips

WTD for current Week

joshua1990 — Tue, 04 Aug 2020 19:35:40 GMT

Hello everyone!

This measure sums upp all sales for the last/ max week of selected month:

Sales WTD = CALCULATE ( [Sales], FILTER ( ALL ( 'Calendar' ), 'Calendar'[Fiscal Year] = MAX ( 'Calendar'[Year] ) && 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) && 'Calendar'[Fiscal Month Number] = MAX ( 'Calendar'[Fiscal Month Number] ) && 'Calendar'[Fis Week] = MAX ( 'Calendar'[Fiscal Week] ) ) )

I would like to get the sales WTD for the current week. How is that possible?

I have a page filter for the current Fiscal Month Number.

Best Regards

Joshua

Re: WTD for current Week

amitchandak — Wed, 05 Aug 2020 03:28:15 GMT

joshua1990 , refer if these can help

Power BI — WTD Questions— Time Intelligence 4–5
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

WTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[Weekday] <=max('Date'[Weekday]))) LWTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -1) && 'Date'[Weekday] <=max('Date'[Weekday])))

Re: WTD for current Week

Greg_Deckler — Tue, 04 Aug 2020 17:59:12 GMT

joshua1990 - Just use WEEKNUM and get everything that has the same WEEKNUM and YEAR.

Re: WTD for current Week

joshua1990 — Tue, 04 Aug 2020 19:37:03 GMT

Greg_Deckler @Thanks Greg! But how?
how would you modify the measure above to get WTD for current week?

Re: WTD for current Week

Greg_Deckler — Tue, 04 Aug 2020 20:34:15 GMT

joshua1990 - Well, first, I would create a "Week" column in my Calendar table. Week = WEEKNUM([Date]). Then, assuming that I have Date from Calendar in my visual I would do something like:

WTD = VAR __Year = YEAR(MAX('Calendar'[Date]) VAR __Week = MAX('Calendar'[Week]) VAR __Weekday = WEEKDAY(MAX('Calendar'[Date])) RETURN SUMX(FILTER(ALL('Sales'),YEAR('Sales'[Date])=__Year && WEEKNUM('Sales'[Date])=__Week && WEEKDAY('Sales'[Date]) <= __Weekday),[Sales])

Something along those lines, there's a bunch of different variations on that theme. I can think of variations using ALLEXCEPT, etc.

Re: WTD for current Week

amitchandak — Wed, 05 Aug 2020 03:41:01 GMT

joshua1990 , I suggest week rank solution as work across week solution, across the year. Also, week num can be used for WTD. Please refer to my blogs, given in my last post.

Re: WTD for current Week

joshua1990 — Wed, 05 Aug 2020 17:26:17 GMT

amitchandak Greg_Deckler : Guys, thank you so much!

But I am asking myself why is my approach not working?

Is there any chance to modify my approach?

I guess I need somehow to get the current week number or year-week number as a filter, right?

Re: WTD for current Week

joshua1990 — Wed, 05 Aug 2020 17:42:09 GMT

amitchandak Thanks, but please read my post carefully.

I don't want to filter for the week. With your providied WTD measure I still have to filter for the week.

I want to get WTD for the current week without filtering anything

Re: WTD for current Week

Greg_Deckler — Wed, 05 Aug 2020 19:51:01 GMT

joshua1990 - It might be but this is part of the reason I don't tend to use CALCULATE because it runs into difficulties once variables and stuff get involved or you start to do anything even remotely complex. Seems like maybe you could add a filter statement for that CALCULATE that did some magic I suppose using TODAY function (if that is how you define "current") so like

'Calendar'[Fiscal Year] = YEAR(TODAY()) for example.

You can use WEEKNUM to get week number and you would also need to use WEEKDAY to get days in the week prior to the current day of the week (in order to get WTD). I've solved this whole thing a number of times in various posts but I don't do it with CALCULATE. Not saying it is impossible but I don't do things that way unless I am forced to because it is fraught with complexities.

Anyway, I wrote an entire blog article about why dealing with CALCULATE is a big PITA.