Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

TTM Measure Not Working

Hi All,

 

I have created a report that displays monthly recurring revenue (MRR) and other retention measures (New MRR, Churn MRR, etc). A requirement has been added to display a trailing twelve months (TTM) view. I created a MRR TTM measure that works fine, but my New MRR TTM measure does not. 

 

I have added information regarding my data model, screenshots of my measures, and resulting output below. Any help is much appreciated in advance!

 

Data Model:

  • Using Date table with Dates field, marked as Date table - 'Date'[Dates]
  • 'RevenueSchedules' is fact table - many-to-one relationship with 'Date' and 'Customers' table

 

Measures:

  • MRR = SUM(RevenueSchedules[Amount])
  • MRR TTM = CALCULATE([MRR], DATESINPERIOD('Date'[Dates], LASTDATE(DATEADD('Date'[Dates], -1, MONTH)), -12, MONTH))
  • New MRR = SUMX(Customers, IF([MRR] > 0 && [MRR TTM] = 0, [MRR]))
  • New MRR TTM = CALCULATE([New MRR], DATESINPERIOD('Date'[Dates], LASTDATE(DATEADD('Date'[Dates], -1, MONTH)), -12, MONTH)) 
 
Output:

 

 

 

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Anonymous ,

     

    I think you can try to calcualte the [New MRR] and [New MRR TTM] by VIRTUAL() table in your code. Due to I don't know your data model, here I will give you some suggestions. The measure should look like as below.

    New MRR TTM =
VAR _VIRTUAL =
    SUMMARIZE (
        Date,
        'Date'[EOMDate],
        "New MRR",
            SUMX ( Customers, IF ( [MRR] > 0 && [MRR TTM] = 0, [MRR] ) )
    )
RETURN
    SUMX (
        DATESINPERIOD (
            'Date'[Dates],
            LASTDATE ( DATEADD ( 'Date'[Dates], -1, MONTH ) ),
            -12,
            MONTH
        ),
        [New MRR]
    )

    If this measure still couldn't help you solve your issue, please share a sample file with me and show me a screenshot with the result you want.

     

    Best Regards,
    Rico Zhou

     

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi Rico,

       

      This did not work as I'm not seeing where you make use of the virtual table code. I've uploaded an example PBI file and expected output CSV file below. 

       

      Expected Result 

      Example