Cumulative monthly totals

[Pallavi_m]

Hi,

I have a Date table and fact table related by field called Year

I want to achieve a table like this:

Year	MonthNo	sales	Monthly Cumulative
2022	01	20	20
2022	02	30	50
2022	03	40	90
2022	04	50	140

I have used this dax to obtain this measure Monthly cumulative:

Monthly cumulative=
calculate(sum(sales),
filter(allselected('Date'),
'Date'[Dates]<=Max('Date'[Dates])))

But the result is 

140

140

140

140

I want to this in a bar graph too.

How do i do this?

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your datamodel looks like, but please try something like below whether it suits your requirement.

Monthly cumulative =
CALCULATE (
    SUM ( sales[revenue] ),
    FILTER ( ALLSELECTED ( 'Date'[Dates] ), 'Date'[Dates] <= MAX ( 'Date'[Dates] ) )
)

[Pallavi_m]

Thanks guys. I got the answer when I was just posting this question. However, the reply button deactivated, when I want to say.. I got the answer.

Thanks again for looking into it.

[Mahesh0016 · Accepted solution]

Monthly cumulative =

calculate(sum(fact_table[Sales]),

filter(allselected('fact_table'),

'fact_table'[Month]<=Max('fact_table'[Month])))

please check your relationship of date and sale table and also filter direction of table.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your datamodel looks like, but please try something like below whether it suits your requirement.

Monthly cumulative =
CALCULATE (
    SUM ( sales[revenue] ),
    FILTER ( ALLSELECTED ( 'Date'[Dates] ), 'Date'[Dates] <= MAX ( 'Date'[Dates] ) )
)