Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Trailing 12 Months YOY

I've got this measure that correctly displays the TTM based on the month end date selected from disconnected date table.

Delivered Net Revenue $ TTM =
VAR CurrentDate = SELECTEDVALUE('Date EOM'[Full Date EOM])
VAR PreviousDate = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-12, DAY(CurrentDate))
VAR Result =
CALCULATE(
[Delivered Net Revenue $],
'dw d_Date'[Full Date EOM] >= PreviousDate && 'dw d_Date'[Full Date EOM] <= CurrentDate
)
RETURN
Result

 

The thought was I could create a clustered column chart and show the current year TTM and previous year TTM next to each other, with the X axis displaying the current year dates from the selected value.  I figured this would do it.

Delivered Net Revenue $ TTM LY =
VAR CurrentDate = SELECTEDVALUE('Date EOM'[Full Date EOM])
VAR PreviousDate = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-12, DAY(CurrentDate))
VAR Result =
CALCULATE(
[Delivered Net Revenue $ LY],
'dw d_Date'[Full Date EOM] >= PreviousDate && 'dw d_Date'[Full Date EOM] <= CurrentDate
)
RETURN
Result

Instead, I'm getting this result

 

I was expecting the previous year results to cluster side by side with the current year.  I'm close, but need a little help to push me over the top.  Thank you in advance!

 

Need Help

4 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi Anonymous 

    Can you share the pbix?

     

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi  Anonymous  ,

    Here are the steps you can follow：

    1. Create measure.

    Delivered Net Revenue $ TTM =
VAR CurrentDate = SELECTEDVALUE('Date EOM'[Full Date EOM])
VAR PreviousDate = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-12, DAY(CurrentDate))
VAR Result =
CALCULATE
    [Delivered Net Revenue $],
    'dw d_Date'[Full Date EOM] >= DATE(YEAR(CurrentDate),1,1) && 'dw d_Date'[Full Date EOM] <= CurrentDate
    )
RETURN
Result
    Delivered Net Revenue $ TTM LY =
VAR CurrentDate = SELECTEDVALUE('Date EOM'[Full Date EOM])
VAR PreviousDate = DATE(YEAR(CurrentDate),MONTH(CurrentDate)-12, DAY(CurrentDate))
VAR Result =
CALCULATE(
    CALCULATE(
    [Delivered Net Revenue $ LY],
    'dw d_Date'[Full Date EOM] >= DATE(YEAR(CurrentDate),1,1) && 'dw d_Date'[Full Date EOM] <= CurrentDate
    ),
    DATEADD('dw d_Date'[Full Date EOM], -1, YEAR))
RETURN
Result

    2. Result:

    Does this result meet your needs?

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.