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 IV

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

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 ?

5 REPLIES 5
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

Helper IV

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?

Regular Visitor

Hi @MasterSonic ,

Add this to the filter condition:

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

Best Regards,

Jay

Helper IV

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

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.

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

)

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 - June 2024

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

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors