Limited WTD to current Day of Week

[Anonymous]

Hello

I've got a WTD calculation working below but need to limit it to only produce WTD data upto the current day of the week. I have a calendar table with calendar date and the day of week and I need to take the Day of Week of the current date from this calendar and use it to limit the below code. 

VAR _CurrentDate = SELECTEDVALUE( 'Calendar'[Calendar Date] ) 
VAR _CurrentWeekYear = SELECTEDVALUE( 'Calendar'[Year Week] ) 

RETURN
CALCULATE(
    [Value],
    FILTER(
        ALL( Calendar ),
        'Calendar'[Year Week] = _CurrentWeekYear &&
        'Calendar'[Calendar Date] <= _CurrentDate
    )
)

Any Ideas ?

I tried this but it didnt work.

VAR _CurrentDate = SELECTEDVALUE( 'Calendar'[Calendar Date] ) 
VAR _CurrentWeekYear = SELECTEDVALUE( 'Calendar'[Year Week] ) 
VAR _DayofWeek = LOOKUPVALUE('Calendar'[Day of Week],'Calendar'[Calendar Date], MAX('Calendar'[Calendar Date]))

RETURN
CALCULATE(
    [Value],
    FILTER(
        ALL( Calendar ),
        'Calendar'[Year Week] = _CurrentWeekYear &&
        'Calendar'[Calendar Date] <= _CurrentDate &&
        'Calendar'[Day of Week] < _DayofWeek
    )
)

[Anonymous · Accepted solution]

Hi  @Anonymous ,

I created some data:

Here are the steps you can follow：

1. Create measure.

Measure =
VAR _CurrentDate = SELECTEDVALUE( 'Calendar'[Calendar Date])
VAR _CurrentWeekYear = SELECTEDVALUE( 'Calendar'[Year Week] )
return
CALCULATE(SUM('Calendar'[Value]),FILTER(ALL('Calendar'),
'Calendar'[Calendar Date]>=
MINX(FILTER(ALL('Calendar'),'Calendar'[Year Week]=_CurrentWeekYear),[Calendar Date])&&'Calendar'[Calendar Date]<=_CurrentDate))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[tamerj1]

Hi @Anonymous 
You may try

WTD =
VAR CurrentWeekYear =
    MAX ( 'Calendar'[Year Week] )
VAR CurrentDayOfWeek =
    MAX ( 'Calendar'[Day of Week] )
RETURN
    CALCULATE (
        [Value],
        'Calendar'[Year Week] = CurrentWeekYear - 1,
        'Calendar'[Day of Week] >= 1,
        'Calendar'[Day of Week] <= CurrentDayOfWeek
    )

[Anonymous]

Hello

Thanks for coming back to me, I've implemented the above but its doesnt give me what I need. What I need to do is limit the WTD to the Todays Day of the Week -1. This is so when this calculation WTD YOY, it only calculates for the same period of time as the current WTD. 

This is so I can compare a WTD against a previous Week and do a like for like comparison. For example; the 19-04-2022 is the 3rd day of this financial week, so I need to calculate a WTD for the first 3 days only for this week and then create a Previous Year WTD, so I can compare growth etc

[amitchandak]

@Anonymous , Using week Rank and week days measure like

WTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[WeekDay]<=max('Date'[WeekDay])-1))
LWTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[WeekDay]<=max('Date'[WeekDay])-1 ))

New columns in date table

new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

Weekday = WEEKDAY([Date],2)

or

Weekday = WEEKDAY([Date],1)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format

Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8