topic Re: cumulative sum or count in DAX Commands and Tips

cumulative sum or count

MasterSonic — Sun, 07 Aug 2022 22:06:25 GMT

Hi guys,

I have 2 tables -

first with uniqe codes

second with dates and category (A,B,C)

I have created this :

Count of dates =

CALCULATE(

sum('Col1'[code]),

FILTER(

ALLSELECTED('Col2'[Date]),

ISONORAFTER('Col2'[Date],MAX('Col2'[Date]), DESC)

)

I am happy with results as next screenshot shows

But after appling filter on category - I have this Count 1 values.
Should not this single values add up to the rest?

Can you help me with that please ?

Re: cumulative sum or count

amitchandak — Mon, 08 Aug 2022 03:15:48 GMT

MasterSonic , You should always use a separate date table(Joined with date of your table) for that. and in visual use columns from that date table

Count of dates =
CALCULATE(
sum('Col1'[code]),
FILTER(
ALLSELECTED('Date'[Date]),
ISONORAFTER('Date'[Date],MAX('Date'[Date]), DESC)
)

Running Total/ Cumulative: https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=41
)

Re: cumulative sum or count

MasterSonic — Mon, 08 Aug 2022 10:59:29 GMT

Hi amitchadak,

I have added new table

Date = CALENDAR(DATE(2015,01,01),DATE(2030,12,31))

And applied your code (

instead of sum I have used count tho, column code has also strings within so sum doesn't work)

- it works but I would like to see smooth rise of overal values
What currenlty I have as a table view are values on the left.
My main goal is to set values like this screenshot from excel in yellow/right.

Re: cumulative sum or count

MasterSonic — Mon, 08 Aug 2022 16:16:30 GMT

I got this one so it works per category.🤠
I think I cannot use filter on visual level, so needed to do this.
Also I referred to dates within my table not to Date table I created previously.

Column = CALCULATE(DISTINCTCOUNT(‘Col1'[code]))

/
10 = CALCULATE(SUM('Col1'[Column]),'Col1'[Category]="C")

cumulative =

var AB = SUM(‘Col1’ [Column])

return

CALCULATE(

'Col1'[10],

FILTER(

ALLSELECTED('Col2'[Date]),

'Col2'[Date]<= MAX('Col2'[Date]]

))

)

Could you just tell me how to do not count blanks from Col2[Date] please?

Re: cumulative sum or count

hircinelol — Wed, 10 Aug 2022 07:54:51 GMT

Hi MasterSonic ,

Add this to the filter condition:

&&'Col2'[Date]<> blank()

Best Regards,

Jay

Re: cumulative sum or count

Anonymous — Wed, 24 Aug 2022 06:39:30 GMT

Hi MasterSonic ,

You can try modifying the function to the following form:

cumulative = var AB = SUM('Col2'[Column]) return CALCULATE( SUM( 'Col1'[10]), FILTER( ALLSELECTED('Col2'[Date]), 'Col2'[Date]<= MAX('Col2'[Date])&& 'Col2'[Date]<>BLANK() ))

Best Regards,

Liu Yang

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