Forum Discussion
WTD Last Year
Dear experts,
I have used this function for WTD this year and it works perfectly.
I tried this to WTD last year and it always returns blank. Then I tried to delete the last fitler Date < Sameday last year, it showed the total sales of the whole same week last year not the WTD.
Can you please help to advise me on this? Where did I do wrong?
Thanks a lot for your help.
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.
5 Replies
- amitchandak
Super User
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.Thanks a lot. I used one of your solutions and it did work
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])))
- v-gizhi-msft
Community Support
Hi,
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
