Visual to include monthly TTM and include total prior TTM

[ptmuldoon]

I'm looking to see how I can mimic an excel report I have that has columns for a TTM plus the Total TTM and Prior year Total TTM.  It should not include the prior year months, only the prior year TTM total.

I like working with the Matrix visual.  Do I need to include separate measures for every month, column or is there a better/easier way?   I know I can quickly add in an date/month and filter by year, but what about adding just the prior year TTM total?

Something like this from excel.

 

 

[ptmuldoon · Accepted solution]

Thanks everyone,

I also found this good youtube video that uses Calculation Groups to add additional totals to a Matrix, and is exactly what I'm trying to do (just need to figure it out !).  

Maximizing Power BI Matrix Visual: Displaying Multiple Totals for Better Insights - Part 1 - YouTube

[Anonymous]

Hi @ptmuldoon,

Glad to hear you may have found a solution! If you're sure the issue has been resolved, could you mark this post as resolved? That way, others with similar issues can more easily find a solution and the community can see that the issue has been resolved.
Thanks, and feel free to reach out if you need further help!

[Anonymous]

Hi All
Firstly, @_AAndrade  thank you for your solution!
And @ptmuldoon ,Based on your question, I've tried to create sample data to replicate what you want to show in the matrix , hopefully it will help you!

Prior Year TTM Sales = 
CALCULATE(
    SUM('Table'[Sales]),
    FILTER(
        ALL('Table'),
        'Table'[Date]<MAX('Table'[Date])  &&
    'Table'[Date]>EDATE(MAX('Table'[Date]) , -12)
    )
)

TTM Sales = 
CALCULATE(
    SUM('Table'[Sales]),
    DATESINPERIOD('Table'[Date], MAX('Table'[Date]) , -12, MONTH)
    
)

I hope this solves your doubts, if you have further questions you can always contact me and I'll get back to nin the first time I hear from you!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

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

[_AAndrade]

Hi @ptmuldoon,

In your PrevYear measure you can put a IF statement like this:
IF(
  ISINSCOPE('YourTable'[Month]),
  BLANK(),
  [PrevYearCalculation]
)