Forum Discussion

dkay84_PowerBI's avatar
dkay84_PowerBI
Microsoft Employee
9 years ago
Solved

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

 

  • dkay84_PowerBI

     

    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

  • Vvelarde's avatar
    Vvelarde
    Community Champion

    dkay84_PowerBI

     

    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 )
        )