Date formatting issue

[Anonymous]

Hi community!
Have an issue to ask you help with.
By the code below I'm trying to get value for the last available day in the previouse year (as you see the hardcoded part - it's Dec 28). 

LastYear Bill FTE = 
CALCULATE (
    SUM (Billed_FTE[BilledFTE]),
    FILTER ( 
        ALLEXCEPT (Billed_FTE, Billed_FTE[Client] ),
        Billed_FTE[Date] = DATE (2018,12,28)
    )
)

Actually, it works. But I need to get rid from that hardcoded part, because next year this date may be different. So I created a new mesure, that gives me a result - 28-Dec-18

LastYearLastDate = 
CALCULATE (
    LASTDATE ( Billed_FTE[Date] ),
    FILTER (
        ALLEXCEPT ( Billed_FTE, Billed_FTE[Client] ),
        Billed_FTE[Year] = MAX ( Billed_FTE[Year] ) -1
    )
)

But when I change my hardcoded date to this [LastYearLastDate] mesure - it doesnt work properly. Looks like there is a date formatting issue and power BI don't recognize my  [LastYearLastDate] mesure as a date or I don't know. 
Please, help! 🙂

[Anonymous]

@v-lid-msft  Thank you soooo much! That works perfectly.

[v-lid-msft · Accepted solution]

Hi @Anonymous ,

Because embed measure into a measure may occur some error regardless of date format, you can try to calculate the date into a var before using it to filter like following.

LastYear Bill FTE = 
VAR d = [BilledFTE]
RETURN
    CALCULATE (
        SUM ( Billed_FTE[Client] ),
        FILTER ( ALLEXCEPT ( Billed_FTE, Billed_FTE[Client] ), Billed_FTE[Date] = d )
    )

BTW, pbix as attached.

Best regards,

Community Support Team _ DongLi
If this post helps, then please consider Accept it as the solution to help the other members find it more