Limiting Week to Date YOY Calculation to same day of week as current WTD

[Anonymous]

Hello,

I have a problem I'm hoping someone can help with, I have a Week to Date (WTD) Calculation that works fine and I have created a YOY version of the measure as well. The problem I have is when current WTD is 3 days long, the WTD YOY calculation is for the whole 7 of that year last week. I have a calendar table with a relative date column I need to use to limit the data to that specific point in time but I need it to take into account the current day of the week in the current WTD. 

To add a second complexity, we use a custom Day of Week (Saturday to Friday) so I can't use the Day of Week function to limite the current WTD.

Below is the current DAX I'm trying but doesnt seem to be working, any help would be appreicated.

VAR _Today = DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))

VAR _YoYDate = LOOKUPVALUE('Calendar'[Calendar Date YOY],
                            'Calendar'[Calendar Date],_Today)

VAR _DayOfWeek = LOOKUPVALUE('Calendar'[Day of Week],
                             'Calendar'[Calendar Date], _YoYDate)
                             
RETURN
                            
CALCULATE(
        [Sales],
        'Calendar'[Relative Week] = 52,
        'Calendar'[Day of Week] < _DayOfWeek

        )

[Anonymous]

Hello,

I need to do this purely in DAX without any calculated columns. I can't get your solution to work 😞

[Whitewater100]

Hi:

'Calendar'[Working Day]

'Calendar'[Day of Week] ),

 'Calendar'[Day of Week Number]  

 'Calendar'[Fiscal Year Week Number]

Then two calculated columns: 

Value For Dates=

VAR LastDateWithData =

    CALCULATE (

        MAX ( 'Fact Table[Key Date] ),

        REMOVEFILTERS ()

    )

VAR FirstDateVisible =

    MIN ( 'Date'[Date] )

VAR Result =

    FirstDateVisible <= LastDateWithData

RETURN

    Result

WTD Calc Column:

Sales WTD =

IF (

    [Value For Dates],

    VAR LastDayOfWeekAvailable =  MAX ( 'Date'[Day of Week Number] )

    VAR LastFiscalYearWeekAvailable = MAX ( 'Date'[Fiscal Year Week Number] )

    VAR Result =

        CALCULATE (

            [YOURMEASURE],

            ALLEXCEPT ( 'Date', 'Date'[Working Day], 'Date'[Day of Week] ),

            'Date'[Day of Week Number] <= LastDayOfWeekAvailable,

            'Date'[Fiscal Year Week Number] = LastFiscalYearWeekAvailable

        )

    RETURN

        Result

)

LY WTD = CALCULATE([Sales WTD], DATEADD(Calendar[Date], -1,YEAR))

I hope this works for you. I'm assuming you have Fact Table and your are measuring something like Sales for this purpose.