Urgent help to fix the Measure issue

[Anonymous]

Hi All,

@amitchandak, @mahoneypat, @daxer-almighty can anyone help me to fix this issue,

I have Dax measures for L1, L4, L13, L52 which calculates total sales value based on the base week selected in the slicer.

Example,

1) If base week(fiscal year & fiscal week) (202052) is selected then the L1 measure calculates total sales by subtracting 1 week that is (202051) 

2) If base week(fiscal year & fiscal week) (202052) is selected then the L4 measure calculates total sales by subtracting 4 weeks that is (202051,202050,202049, 202048) 

3) If base week(fiscal year & fiscal week) (202052) is selected then the L13 measure calculates total sales by subtracting 13 weeks that is (202051,202050,202049, 202048,202047,202046,202045,202044,202043,202042,202041,202040) 

4) If base week(fiscal year & fiscal week) (202052) is selected then the L13 measure calculates total sales by subtracting 13 weeks that is (202051 to 202001)

Dax Measures 

1)  

L1 = CALCULATE([Total Sales],FILTER(Catman_Calendar,and(((Catman_Calendar[CY_BASEWEEK])=(MAX( Weeklist[Base Week]))),((Catman_Calendar[CY_BASEWEEK])=([CY_BaseWeek_Selected])))))

2) 

L4 =

(CALCULATE([Total Sales],FILTER(Catman_Calendar,and(((Catman_Calendar[CY_BASEWEEK])>(MAX( Weeklist[Base Week])-4)),

((Catman_Calendar[CY_BASEWEEK])<=([CY_BaseWeek_Selected]))))))

3) 

L13 =

(CALCULATE([Total Sales],FILTER(Catman_Calendar,and(((Catman_Calendar[CY_BASEWEEK])>(MAX( Weeklist[Base Week])-13)),((Catman_Calendar[CY_BASEWEEK])<=([CY_BaseWeek_Selected]))))))

4) 

L52 =

(CALCULATE([Total Sales],FILTER(Catman_Calendar,and(((Catman_Calendar[CY_BASEWEEK])>(MAX( Weeklist[Base Week])-52)),((Catman_Calendar[CY_BASEWEEK])<=([CY_BaseWeek_Selected]))))))

Until the 202052 formula was working fine after the fiscal year changed to 202101 from there these formula is not working so I need help to fix this issue.

I have attached the screenshots below for reference 

[Anonymous]

@amitchandak  & @lbendlin  Thanks for sharing your inputs i will try from my end. Also, if you can help me with the logic it will be good 

[amitchandak]

@Anonymous , Have you checked the Week Rank method with a separate date and calendar table in my blog and video? See if that can help.

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

[lbendlin]

A year has 52 (plus /minus) weeks, not 100.  You need to adjust your math accordingly, or use a different column for your week numbers to make them contiguous.