Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Date formatting issue

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

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    7 years ago

    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

2 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    7 years ago

    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