Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!To celebrate FabCon Vienna, we are offering 50% off select exams. Ends October 3rd. Request your discount now.
I am trying to calculate sales YTD (using week number) vs. sales Last Year YTD. I can't use SAMEPERIODLASTYEAR because I have a custom FY calendar. My data will only ever have the current FY (YTD) and the full prior FY.
I have a measure, "Latest Week YTD", that calculates the latest week number of the current FY. Lets say this measure returns the number 12 currently.
Latest Week YTD =
CALCULATE (
MAX ( Table1[Week Number] ),
FILTER ( ALL ( Table1[FY] ), Table1[FY] = MAX ( Table1[FY] ) )
)
I also have a measure, "Last Year", that returns the previous FY. Lets say this measure returns the number 2016 currently:
Last Year =
CALCULATE ( MIN ( Table1[FY] ), ALL ( Table1[FY] ) )
I am trying to create a measure that will dynamically calculate the sales for the previous FY using the week number returned by the Latest Week YTD measure:
Last YTD Sales =
CALCULATE (
SUM ( Table[Sales] ),
FILTER ( ALL ( Table[FY] ), Table[FY] = [LastYear] ),
FILTER ( ALL ( Table[Week Number] ), Table[Week Number] <= [Latest Week YTD] )
)
My problem is that this last measure only returns the full amount for the entire previous FY. If I change the last filter arguement from
Table[Week Number] <= [Latest Week YTD]
to
Table[Week Number] <= 12
it works fine.
Can someone please tell me what I am missing?
Thanks
Solved! Go to Solution.
hi, With this modifcation should be work.
Last YTD Sales = VAR LY = [Last Year] VAR LW = [Latest Week YTD] RETURN CALCULATE ( SUM ( Table1[Sales] ), FILTER ( ALL ( Table1 ), Table1[FY] = LY && Table1[Week Number] <= LW ) )
Hi @dkay84_PowerBI,
You should create the measure using the formula @dkay84_PowerBI posted. Please feel free to ask if you have other issue.
Best Regards,
Angelia
hi, With this modifcation should be work.
Last YTD Sales = VAR LY = [Last Year] VAR LW = [Latest Week YTD] RETURN CALCULATE ( SUM ( Table1[Sales] ), FILTER ( ALL ( Table1 ), Table1[FY] = LY && Table1[Week Number] <= LW ) )