DAX WTD HELP

[batman]

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

B

[batman]

Thank you guys, much appreciated.

[jiaopengzi]

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

[batman]

Is there an English version of this? 

[amitchandak]

@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])))

[batman]

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