Forum Discussion
Incremental into cumulative
Hi
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 ?
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.
5 Replies
smpa01Community Champion
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] )- Greg_Deckler
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. 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 - v-robertq-msft
Community Support
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.
