DAX help

[dkay84_PowerBI]

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

[v-huizhn-msft]

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

[Vvelarde · Accepted solution]

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