Forum Discussion
WTD for current Week
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.
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.