Forum Discussion

PavelR's avatar
PavelR
Icon for Solution Specialist rankSolution Specialist
9 years ago
Solved

YTD last year DAX

Hi,   I have question about YTD Last year DAX formula. Even though I have read many blogs and topics here in community, I still don't know how to solve this issue. Suppose I have data like below. ...
Need Help
  • OwenAuger's avatar
    OwenAuger
    9 years ago

    PavelR

     

    It should work fine in Power BI.

    You didn't have any dates in your Data table beyond 3 Jan 2017 did you?

     

    Here is a sample PBIX file with your data posted above where the measure is working:

    PBIX file

     

    I made a small change to the YTD measure so that it is not displayed after the max date in Data table, but the YTD LY measure is the same as I posted.

     

    Have a play with that - there must be some difference in your model if it is not working.

     

    Owen

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
9 years ago

PavelR

 

It sounds like you want your YTD LY measure to detect the last date that appears in your Data table, and only translate dates up to that date back to last year within SAMEPERIODLASTYEAR.

 

You can use a pattern like this (note the distinction between Data[Date] which I assume is a column of Data, and Date[Date]):

 

YTD LY (limited by last date in Data table) =
VAR DataMaxDate =
    CALCULATE ( MAX ( Data[Date] ), ALL ( Data ) )
RETURN
    CALCULATE (
        [YTD],
        SAMEPERIODLASTYEAR (
            INTERSECT (
                VALUES ( Date[Date] ),
                DATESBETWEEN ( Date[Date], BLANK (), DataMaxDate )
            )
        )
    )

DataMaxDate is the global maximum date in your Data table. You can define it differently if you want to apply different logic.

 

Cheers,

Owen :)

  • PavelR's avatar
    PavelR
    Icon for Solution Specialist rankSolution Specialist
    9 years ago

    Hi OwenAuger, thanks for your response,

     

    when I implement your measure to PBI I get the same value of 44, as in previous YTD LY measure. Am I wrong with something? Is there some additional prerequisite in PBI or something?

     

    Thanks and regards

    Pavel

     

    • OwenAuger's avatar
      OwenAuger
      Icon for Super User rankSuper User
      9 years ago

      PavelR

       

      It should work fine in Power BI.

      You didn't have any dates in your Data table beyond 3 Jan 2017 did you?

       

      Here is a sample PBIX file with your data posted above where the measure is working:

      PBIX file

       

      I made a small change to the YTD measure so that it is not displayed after the max date in Data table, but the YTD LY measure is the same as I posted.

       

      Have a play with that - there must be some difference in your model if it is not working.

       

      Owen

      • PavelR's avatar
        PavelR
        Icon for Solution Specialist rankSolution Specialist
        9 years ago

        Awesome OwenAuger, thanks a lot!

         

        I really appreciate your effort. I will try it implement to my PBI file.

         

        Could you also explain to me please, why the function SAMEPERIODLASTYEAR doesn't do that comparison as default behavior? I mean the comparison of the corresponding previous period? Why it compute YTD LY as the whole month (value 44)?

         

        I think that my requirement ("business case") is quite standard, but the solution is not so easy :smileyhappy:

         

        Regards.

        Pavel