Forum Discussion
WTD for current Week
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
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.
8 Replies
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/1051123WTD = 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])))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.
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?
joshua1990 - Just use WEEKNUM and get everything that has the same WEEKNUM and YEAR.
Greg_Deckler @Thanks Greg! But how?
how would you modify the measure above to get WTD for current week?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.
