i want to calculate cumulative total, even for months without value

[Tian_Tian_Qiu]

Hi Gurus,

i want to calculate cumulative trailling twelve months total.
The rule is = If the there are 12 month from the current month, then calculate total number of 12 months total, if there are less than 12 month from the current month, calculate total of all the previous months. 

i.e. 
2020/04/30 numberofterminations= 5, totalttm=5
2020/5/31 numberofterminations = 1 totalttm=5+1=6

but the problem is that 2020/07/31 is not having number of terminations, the totalttm should be 8,  so how to calculate ?

similarly, 2021/06/30 totalttm should be 22, which should be the sum of the number from 2020/07/31 till 2021/06/30

Thank you!

[Tian_Tian_Qiu · Accepted solution]

@v-yiruan-msft @Greg_Deckler  ， i think i finally worked it out by myself, however thank you for your kind help!

[Greg_Deckler]

@Tian_Tian_Qiu Perhaps:

Measure =
  VAR __MonthDate = MAX('Table'[employee group common Year-Month (end of month)])
  VAR __12MonthsPrior = EOMONTH(__MonthDate,-12)
RETURN
  SUMX(FILTER(ALL('Table',[employee group common Year-Month (end of month)] >= __12MonthsPrior && [employee group common Year-Month (end of month)]<= __MonthDate),[number of terminations])

[Tian_Tian_Qiu]

@Greg_Deckler Thank you for helping out!
i have applied the change, but this time the month without number of terminations is showing blank result and i'm really confused why the total is correct however the number for hourly is empty:

as you can see, in green, the hourly paid for 2020/07/30 is blank and my wish is to show the totalttm for Hourly to be 8, the salaried paid number is correct, and total (in yellow, to the right ) is the sum of hourly and salary paid = 8+41 = 49
Is this a bug of power bi??
btw, i have changed the formula into:

totalttm12terminationnewnew =

var _monthdate = MAX('GET Jane hires and exits - Terminations TTM'[Year-Month(end of month)])

var _12monthsprior = EOMONTH(_monthdate, -12)

return

sumx(CALCULATETABLE('get jane hires and exits - terminations ttm', 'get jane hires and exits - terminations ttm'[Year-Month(end of month)]>=_12monthsprior && 'get jane hires and exits - terminations ttm'[Year-Month(end of month)]<=_monthdate),[numberofterminations])

[v-yiruan-msft]

Hi @Tian_Tian_Qiu ,

You can update the measure as below and check whether it can get the correct result. Please note that the part with red font is updated one.... You can find the details in the attachment.

totalttm12terminationnewnew =
VAR _monthdate =
    MAX ( 'GET Jane hires and exits - Terminations TTM'[Year-Month(end of month)] )
VAR _12monthsprior =
    EOMONTH ( _monthdate, -11 )
VAR _pre12month =
    CALCULATE (
        MAX ( 'get jane hires and exits - terminations ttm'[Year-Month(end of month)] ),
        FILTER (
            ALLSELECTED ( 'get jane hires and exits - terminations ttm' ),
            'get jane hires and exits - terminations ttm'[Year-Month(end of month)] = _12monthsprior
        )
    )
VAR _mmindate =
    CALCULATE (
        MIN ( 'get jane hires and exits - terminations ttm'[Year-Month(end of month)] ),
        ALLSELECTED ( 'get jane hires and exits - terminations ttm' )
    )
RETURN
    SUMX (
        FILTER (
            ALLSELECTED ( 'get jane hires and exits - terminations ttm' ),
            'get jane hires and exits - terminations ttm'[Year-Month(end of month)]
                >= IF ( ISBLANK ( _pre12month ), _mmindate, _12monthsprior )
                && 'get jane hires and exits - terminations ttm'[Year-Month(end of month)] <= _monthdate
        ),
        [numberofterminations]
    )

If the above one is not your expected one, please provide some sample data (exclude sensitive data) and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file with me. You can refer the following thread to upload your file in the community. Thank you.

How to upload PBI in Community

Best Regards

[Tian_Tian_Qiu]

hi - i have tried this approach, but again the total is empty for the months without terminations

[v-yiruan-msft]

Hi @Tian_Tian_Qiu ,

Could you please share a simplified pbix file with me in order to make troubleshooting and provide you a suitable solution? You can refer the following thread to upload your file in the community. Thank you.

How to upload PBI in Community

Best Regards