topic Re: DAX WTD HELP in DAX Commands and Tips

DAX WTD HELP

batman — Wed, 17 May 2023 08:17:51 GMT

Greetings everyone,

I require some help with DAX. I am currently displaying the Year, Week, and Date in a Matrix Table, allowing for drill-down functionality. I am in need of writing a calculation for a Measure that accumulates values and resets every week. It's important to note that my week starts on Monday. Below is the measure I have written for the current year. In the second column, I need a calculation that represents the same value for the prior year. I would greatly appreciate any assistance or guidance provided. Thank you in advance.

WTD CY = CALCULATE(sum(Tab1[Dollars]), FILTER(ALL('Calendar'),'Calendar'[Week Rank]=max('Calendar'[Week Rank]) && 'Calendar'[Weekday] <=max('Calendar'[Weekday])))

Additionally, I have created some additional measures for testing purposes. However, I have encountered an issue where these measures do not apply the desired filters when modifications are made on a slicer.

DOL WTD =

VAR CurrentDate = LASTDATE('Calendar'[Date])

VAR DayNumberOfWeek = 'Calendar'[Day Number of Week for Date]

RETURN

CALCULATE(

SUM ( Tab1[Dollars]),

DATESBETWEEN(

'Calendar'[Date],

DATEADD(

CurrentDate,

-1 * (DayNumberOfWeek - 1),

DAY

CurrentDate

)

and

DOL WTD LY =

VAR CurrentDate = LASTDATE('Calendar'[Date])

VAR DayNumberOfWeek = WEEKDAY(CurrentDate, 2)

VAR StartDate = DATEADD(

CurrentDate,

-1 * (DayNumberOfWeek - 1),

DAY

)

VAR StartDateLY = DATEADD(

StartDate,

-364,

DAY

)

RETURN

CALCULATE(

SUM(Tab1[Dollars]),

FILTER(

ALL('Calendar'),

'Calendar'[Date] >= StartDateLY && 'Calendar'[Date] <= DATEADD(CurrentDate, -364, DAY)

)

Thanks once again

Re: DAX WTD HELP

amitchandak — Wed, 17 May 2023 16:01:37 GMT

batman , have column year, week, and weekday. try like

WTD LY = CALCULATE(sum(Tab1[Dollars]), FILTER(ALL('Calendar'),'Calendar'[Year]=max('Calendar'[Year]) -1 && 'Calendar'[Week]=max('Calendar'[Week]) && 'Calendar'[Weekday] <=max('Calendar'[Weekday])))

Re: DAX WTD HELP

jiaopengzi — Thu, 18 May 2023 00:58:00 GMT

The meaning of wtd is roughly as follows, please refer to it.

```

VAR DATE_END_AC =
LASTDATE ( '01_Calendar'[dates] )
VAR N_W =
WEEKDAY ( DATE_END_AC, 3 ) // Monday：0，Sunday：6
VAR DATE_START_AC =
DATEADD ( DATE_END_AC, - N_W, DAY )
VAR DATE_TABLE_AC =
DATESBETWEEN ( '01_Calendar'[dates], DATE_START_AC, DATE_END_AC )
VAR WTD0 =
CALCULATE ( SELECTEDMEASURE (), DATE_TABLE_AC )
```

There are more time dimensions, see the picture below.

https://jiaopengzi.com/2653.html

Re: DAX WTD HELP

batman — Thu, 18 May 2023 13:08:21 GMT

Thank you guys, much appreciated.

Re: DAX WTD HELP

batman — Thu, 18 May 2023 15:22:55 GMT

Is there an English version of this?

Re: DAX WTD HELP

batman — Mon, 22 May 2023 15:24:05 GMT

amitchandak - Thank you for providing feedback, I was able to make it work with a similar formula

DOLLARS WTD LY = CALCULATE(

sum(Tab1[Dollars]),

FILTER(

ALL('Calendar'),

'Calendar'[Week Rank] = max('Calendar'[Week Rank]) - 52

&& 'Calendar'[Weekday] <= max('Calendar'[Weekday])

)

However once I roll it up to yearly level it just take the last number of the last week. Have you ran into this challange before?