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

Helper II

## Calculating Cumulative Monthly Totals

Hello Power BI Gurus

I am stuck up with a situation, for which I have seen many solutions. However, nothing worked for me as I have more columns in my table. I need to calculate Monthly Cumulative numbers that add up values for each month in respective codes. I have following table structure:

I need a new calculated field that creates cumulative monthly "Actual_KD" filed for each Account Code and Cost Center.

I have tried following formulae but it gives me zero values all the way (TB is my Table name):

```Cumulative_Actual =
CALCULATE (
SUM ( TB'[Actual_KD] ),
ALL ( 'TB' ),
'TB'[Month] <= EARLIER ( 'TB'[Month] )
)```

Appreciate support of experts

Regards

1 ACCEPTED SOLUTION
Memorable Member

@Waseem, oh i'm sorry for missing in quickly typing. there is misssing filter in the expression:

```Cumulative_Actual =
CALCULATE (
SUM ( 'TB'[Actual_KD] ),
filter( ALL ( 'Dates' ),
'Dates'[Date] <= MAX( 'Dates'[Date] ))
)```

please kindly try again with calculated measure

18 REPLIES 18
Frequent Visitor

I need a column where it has to show the count as per the MonthNo. Need help Urgent

Frequent Visitor

sorry i was not clear earlier. how about if the project extends for next year. please see below picture

Super User

Hi,

This calculated column formula works

``=DATEDIFF(CALCULATE(MIN(Table1[Month]),FILTER(Table1,Table1[Project]=EARLIER(Table1[Project]))),Table1[Month],MONTH)+1``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Hi,

Write this calculated column formula

=month(Data[Month])

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper I

Hello Guys,

Thanks for all, I resolved  this problem with Dax bellow. Best Regards.

```New Date Table:

New Measure:

SUM(Relatorio_Anual_2017[Horas]),
FILTER(
ALLSELECTED('Calendar'[Date]),
'Calendar'[Date] <= MAX ('Calendar'[Date])
)
)```
Helper I

Hello guys,

How are you? I need your help for same problem.

I used same DAX sample, but this not worked for me, can you help me?

I have following table structure.

Thank you very much.

Frequent Visitor

How did you get a dark gray background for your visualisation pane?

Memorable Member

Hi @Waseem,
Instead of using Calculated Column, you could use Calculated Measure:

• Created new Dates table: Dates= Calendarauto()
• Making relationship between fact and dates table
• Create calculated measure:
```Cumulative_Actual =
CALCULATE (
SUM ( 'TB'[Actual_KD] ),
ALL ( 'Dates'),
'Dates'[Date] <= MAX ( 'Dates'[Date] )
)```

Please refer my example as a part of topic: https://community.powerbi.com/t5/Desktop/DAX-Count-of-Stores-that-are-under-the-Average/td-p/100685

In case you still want to go on with Calculated Column, you could try replace method ALL with ALLEXCEPT(TB,columnyouwantfilter1,columnyouwantfilter2)

Please feel free to show your expectation in picture or let me know if you need a sample to clarify any concern. If this works for you please accept it as solution and also like to give KUDOS.

Anonymous
Not applicable

Thank you so much for this input that create measure not column to get the desired result. 🙂 Finally, this got my work done.

Helper II

Sorry if it is not legible. The error reads like following:

"A Function MAX has been used in the True/False expression that is used as a Table Filter expression. This is not allowed".

Regards

Memorable Member

@Waseem, oh i'm sorry for missing in quickly typing. there is misssing filter in the expression:

```Cumulative_Actual =
CALCULATE (
SUM ( 'TB'[Actual_KD] ),
filter( ALL ( 'Dates' ),
'Dates'[Date] <= MAX( 'Dates'[Date] ))
)```

please kindly try again with calculated measure

New Member

Great, this solution works, but what if you want to throw in a dimension at the legend level, this does not seem to work and is only multiplying and duplicating values. How can this formula be modified in the process?

Helper II

@tringuyenminh92 Many Thanks.

Helper I

Hello @Waseem

I have the same problem, can you help me too? I used the same code, but this not worked for me

Thank you very much 😉

I have following table structure.

Helper II

You need to create a date table first and give it name "Date". You can create this table as below:

Date = CALENDARAUTO()

Then type following formula to crate a "New Measure":

Cumulative = IF(COUNTROWS(Relatorio_Completo_2017)<0,CALCULATE(SUM(Relatorio_Completo_2017[Hours]),FILTER(ALL('Date'),'Date'[Date}<=MAX{'Date'[Date]))),BLANK())..

Hope it helps

Cheers

Memorable Member

You should create Dates table by using Calendar() or CalendarAuto() method, and making relationship between your transaction table with this Dates table. Then apply above formula. please notice that we put filter on Dates table, not on transaction table.

In case this is still not working, please share your current working file and i could quickly check it for you.

Helper I

Hello @tringuyenminh92 and @Waseem,

Thank you for your help 🙂

I tried to create but it did not work, it follows the same files I'm using to create the BurnDown graphic.

Thanks again for the help 🙂

Helper II

Thanks a lot for your prompt response. I tried to do what you suggested but there was an error prompt. I created both a measure and a column but ended up with same error message. Below is the snapshot of my dashboard. Appreciate your help.

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.