cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

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

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!

1 ACCEPTED SOLUTION
Regular Visitor

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

6 REPLIES 6
Regular Visitor

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

Super User

@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])``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Regular Visitor

@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])

Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

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

Community Support

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

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

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors