Forum Discussion

dtg87's avatar
dtg87
Frequent Visitor
5 years ago

Last Year To Date Using ISO Calendar

Hi,   We work to an ISO Retail Week Calendar, where RetailDayofYear compares with the corresponding RetailDayofYear of last year. This differs during a 53 week year (2016,2020) when Week 53 will co...
Need Help
amitchandak's avatar
amitchandak
Super User
5 years ago

dtg87 , with a separate date table with column retaildayofyear .separate  table is must for below logic

 


YTD= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[retaildayofyear ] <= Max('Date'[retaildayofyear ]) ))
LYTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[retaildayofyear ] <= Max('Date'[retaildayofyear ])))

 

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

 

isoweek = weeknum([date],21)

 

refer if needed

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

dtg87's avatar
dtg87
Frequent Visitor
5 years ago

Thanks for this, but this isn't quite what i need.

I cannot do YEAR -1 to get last year in these circumstances.

Week 53 in 2020 Compares to Week 1 in 2020.

Therefore for comparing 2020 Weeks 1 - 53, they would need to filter the table on 2019 wks1 to 52 + 2020 wk1

So the MIN comparison would be 2019 01 to 2020 01