topic Re: Incremental into cumulative in DAX Commands and Tips

Incremental into cumulative

Anonymous — Mon, 01 Nov 2021 13:28:49 GMT

I have been trying solutions but i just don't get it

I want to do the following on Power BI

Like a sum with condition on date and on the the same product x lob

I tried TotalYTD but it didn't work.

Does someone knows how to do it ?

Re: Incremental into cumulative

Greg_Deckler — Mon, 01 Nov 2021 14:19:45 GMT

Anonymous See if this helps: S Excel to DAX Translation - Microsoft Power BI Community

Otherwise, Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Incremental into cumulative

Anonymous — Mon, 01 Nov 2021 16:27:59 GMT

Thanks I'll have a look.

The data is below. The output is col "Cumulative Amount 1"

It sums "amount" if it's the same product and Lob for all valuation date anterior to the one in the line

Product	LoB	Valuation Date	Amount	Amount 2	Month	Cumulative Amount 1
A	Accident	01/01/2021	10	100	January	10
A	Accident	01/02/2021	20	200	February	30
A	Accident	01/03/2021	30	300	March	60
A	Accident	01/04/2021	40	400	April	100
A	Accident	01/05/2021	50	500	May	150
A	Accident	01/06/2021	60	600	June	210
A	Accident	01/07/2021	70	700	July	280
A	Accident	01/08/2021	80	800	August	360
A	Accident	01/09/2021	90	900	September	450
A	Accident	01/10/2021	100	1000	October	550
A	Motor	01/01/2021	150	250	January	150
A	Motor	01/02/2021	200	300	February	350
A	Motor	01/03/2021	250	350	March	600
A	Motor	01/04/2021	300	400	April	900
A	Motor	01/05/2021	350	450	May	1250
A	Motor	01/06/2021	400	500	June	1650
A	Motor	01/07/2021	450	550	July	2100
A	Motor	01/08/2021	500	600	August	2600
A	Motor	01/09/2021	550	650	September	3150
A	Motor	01/10/2021	600	700	October	3750

Re: Incremental into cumulative

Anonymous — Mon, 01 Nov 2021 18:18:30 GMT

OK so i found this

Cumulative Total =

CALCULATE (

SUM(Sheet1[Amount]),

FILTER (

ALL( Sheet1),

Sheet1[Valuation Date] <= EARLIEST ( Sheet1[Valuation Date])))

Which give me the cumulative sum by valuation date but i cannot add filter.

Can someone help

Re: Incremental into cumulative

smpa01 — Mon, 01 Nov 2021 19:35:27 GMT

Anonymous you can use the following measure

Measure = CALCULATE ( SUM ( 'Table'[Amount] ), FILTER ( ALL ( 'Table' ), 'Table'[Valuation Date] <= MAX ( 'Table'[Valuation Date] ) ), KEEPFILTERS ( SUMMARIZE ( 'Table', 'Table'[Product], 'Table'[LoB] ) ) )

If you need calculated column

cumulativeSum = SUMX ( FILTER ( 'Table', 'Table'[Product] = EARLIER ( 'Table'[Product] ) && 'Table'[LoB] = EARLIER ( 'Table'[LoB] ) && 'Table'[Valuation Date] <= EARLIER ( 'Table'[Valuation Date] ) ), 'Table'[Amount] )

Re: Incremental into cumulative

v-robertq-msft — Tue, 02 Nov 2021 09:50:36 GMT

Hi, Anonymous

According to your description, I can roughly understand your requirement, you want to sum the "amount" if it's the same product and Lob for all valuation date anterior to the one in the line and create a slicer to filter the data based on the date column, right? I think you can create a measure to use the AllSelected() function instead of All() like this to achieve your requirement:

Cumulative Amount 1 = CALCULATE(SUM('Table'[Amount]),FILTER(ALLSELECTED('Table'),[LoB]=MAX('Table'[LoB])&&[Valuation Date]<=MAX('Table'[Valuation Date])))

And you can get what you want, like this:

You can download my test pbix file below

For more info about the Allselected() function in DAX, please refer to this link:

https://docs.microsoft.com/en-us/dax/allselected-function-dax

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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