Forum Discussion
WTD Last Year
Tan_Bui , refer to my blog. I have not last year there , but you can try these two calculation
LYWTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52) && 'Date'[Weekday] <=max('Date'[Weekday]))) Or LYWTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=(max('Date'[Year]) -1) && 'Date'[Week Number]=(max('Date'[Week Number])) && 'Date'[Weekday] <=max('Date'[Weekday])))Please Watch/Like/Share My webinar on Time Intelligence: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
My Youtube Tips at: https://www.youtube.com/playlist?list=PLPaNVDMhUXGYrm5rm6ME6rjzKGSvT9Jmy
Appreciate your Kudos.
Community SupportHi,
Please take following steps:
1)Create a WeekNum column:
WeekNum = WEEKNUM('Table'[Date])
2)Try these two measures:
WTD(Current Year) =
IF (
YEAR ( MAX ( 'Table'[Date] ) ) = YEAR ( TODAY () ),
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[WeekNum] IN FILTERS ( 'Table'[WeekNum] )
&& 'Table'[Date] <= MAX ( 'Table'[Date] )
)
)
)WTD(Last Year) =
IF (
YEAR ( MAX ( 'Table'[Date] ) )
= YEAR ( TODAY () ) - 1,
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[WeekNum] IN FILTERS ( 'Table'[WeekNum] )
&& 'Table'[Date] <= MAX ( 'Table'[Date] )
)
)
)
3)The result shows:
See my attached pbix file.
Best Regards,
Giotto
v-gizhi-msft thanks a lot for your thorough instructions including the pbix file. Appreciate that.
However, I used the card to display the last year WTD and it would show blank.
- v-gizhi-msft
Hi,
Please try this:
WTD(Last Year) = SUMX ( DISTINCT ( 'Table'[Date] ), CALCULATE ( IF ( YEAR ( MAX ( 'Table'[Date] ) ) = YEAR ( TODAY () ) - 1, CALCULATE ( SUM ( 'Table'[Value] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[WeekNum] IN FILTERS ( 'Table'[WeekNum] ) && 'Table'[Date] <= MAX ( 'Table'[Date] ) ) ) ) ) )The result shows:
See my attached pbix file.
Best Regards,
Giotto
