I've been struggeling with something. I want a calculated column that calculates the sales of that month based on the cumulitive sales.
ID Month Year CumAmount Sales
1484 2019-jan 120000 -
1484 2019-feb 130000 10000
1484 2019-mar 135000 5000
Unfortunately I don't have a datevalue in the same row, but I do have "2019-jan", "2019-feb" etc. that I have connected with the same data format in a calendar table.
Can someone help me out?
Go to Solution.
Hi @TimHellegers ,
For your requirement, please create an index column in Query Editor firstly and then create the calculated column with the formula below.
VAR a =
MAX ( 'Table1'[CumAmount] ),
FILTER ( ALL ( 'Table1' ), 'Table1'[Index] = EARLIER ( Table1[Index] ) - 1 )
IF ( ISBLANK ( a ), BLANK (), 'Table1'[CumAmount] - a )
Here is the output.
You also could have a reference of my attachement.
I have the same problem but the other way round...
I have the sales amount per month and would like to show on graph the cumulative.
For exemple :
... and so on.
Could you please help me ?
You can create a calculated column for that;
Running Total COLUMN =
SUM ( 'TableName'[Value] ),
ALL ( 'Tablename' ),
'TableName'[Date] <= EARLIER ( 'TableName'[Date] )
Power BI Super User, Greg Deckler, explains
Register by September 5 to save $200
Engage and empower students with Power BI!
Continue your learning in our online communities.