Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi everyone,
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?
Solved! Go to Solution.
Hi @Anonymous ,
For your requirement, please create an index column in Query Editor firstly and then create the calculated column with the formula below.
Column = VAR a = CALCULATE ( MAX ( 'Table1'[CumAmount] ), FILTER ( ALL ( 'Table1' ), 'Table1'[Index] = EARLIER ( Table1[Index] ) - 1 ) ) RETURN IF ( ISBLANK ( a ), BLANK (), 'Table1'[CumAmount] - a )
Here is the output.
You also could have a reference of my attachement.
Best Regards,
Cherry
Hi @Anonymous ,
For your requirement, please create an index column in Query Editor firstly and then create the calculated column with the formula below.
Column = VAR a = CALCULATE ( MAX ( 'Table1'[CumAmount] ), FILTER ( ALL ( 'Table1' ), 'Table1'[Index] = EARLIER ( Table1[Index] ) - 1 ) ) RETURN IF ( ISBLANK ( a ), BLANK (), 'Table1'[CumAmount] - a )
Here is the output.
You also could have a reference of my attachement.
Best Regards,
Cherry
Hello,
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 :
Feb=Jan+Feb
March=Jan+Feb+March
... and so on.
Could you please help me ?
Thanks,
Hi,
You can create a calculated column for that;
Running Total COLUMN = CALCULATE ( SUM ( 'TableName'[Value] ), ALL ( 'Tablename' ), 'TableName'[Date] <= EARLIER ( 'TableName'[Date] ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |