Forum Discussion
DAX help
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
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 ) )
2 Replies
- VvelardeCommunity Champion
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 ) ) - v-huizhn-msftMicrosoft Employee
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