cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MasterSonic
Helper III
Helper III

cumulative sum or count

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

MasterSonic_0-1659909271323.png

 

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 ?

MasterSonic_1-1659909303398.png

 

 

 

 

5 REPLIES 5
v-yangliu-msft
Community Support
Community Support

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

MasterSonic
Helper III
Helper III

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?

 

MasterSonic_0-1659975296768.png

 

Hi @MasterSonic ,

 

Add this to the filter condition:

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

 

Best Regards,

Jay

MasterSonic
Helper III
Helper III

Hi amitchadak,

I have added new table 

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

MasterSonic_0-1659955307324.png
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.

MasterSonic_1-1659955575732.png

 

amitchandak
Super User
Super User

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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors