cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Prodigy

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

1 ACCEPTED SOLUTION
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
8 REPLIES 8
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Post Prodigy

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

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Super User

@joshua1990 , refer if these can help

``````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])))``````
Super User

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

Post Prodigy

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

Post Prodigy

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

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.