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 all,
I need to find the accumulated value of an operation. I have this table:
COUNT | NAME | DATE | H | D | BALANCE |
52000028 | BANK1 | 31/12/2018 | 19.000,00 | - 19.000,00 | |
01/01/2019 | 1.640,00 | - | - 17.360,00 | ||
01/02/2019 | 1.635,00 | - | - 15.725,00 | ||
01/03/2019 | 1.630,00 | - | - 14.095,00 | ||
01/04/2019 | 1.625,00 | - | - 12.470,00 | ||
01/05/2019 | 1.620,00 | - | - 10.850,00 | ||
01/06/2019 | 1.615,00 | - | - 9.235,00 | ||
01/07/2019 | 1.610,00 | - | - 7.625,00 | ||
01/08/2019 | 1.605,00 | - | - 6.020,00 | ||
01/09/2019 | 1.600,00 | - | - 4.420,00 | ||
01/10/2019 | 1.595,00 | - | - 2.825,00 | ||
01/11/2019 | 1.590,00 | - | - 1.235,00 | ||
01/12/2019 | 21.120,00 | - 22.355,00 | |||
TOTAL | 17.765,00 | 40.120,00 | - 22.355,00 |
and I want to find the column BALANCE that is equal to = Previous balance -D + H with the condition that if it is the first row of the group (COUNT) then it is equal to = -D + H. What is the DAX formula to get it?
Thank you.
hi, @Raul
You could use EARLIER Function in this formula to add a new column'
Column = VAR _firstdate = CALCULATE ( MIN ( 'Table'[DATE] ), FILTER ( 'Table', 'Table'[COUNT] = EARLIER ( 'Table'[COUNT] ) && 'Table'[NAME] = EARLIER ( 'Table'[NAME] ) ) ) RETURN IF ( _firstdate = 'Table'[DATE], - 'Table'[D] + 'Table'[H], - CALCULATE ( SUM ( 'Table'[D] ), FILTER ( 'Table', 'Table'[DATE] = _firstdate && 'Table'[COUNT] = EARLIER ( 'Table'[COUNT] ) && 'Table'[NAME] = EARLIER ( 'Table'[NAME] ) ) ) + CALCULATE ( SUM ( 'Table'[H] ), FILTER ( 'Table', 'Table'[COUNT] = EARLIER ( 'Table'[COUNT] ) && 'Table'[NAME] = EARLIER ( 'Table'[NAME] ) && 'Table'[DATE] <= EARLIER ( 'Table'[DATE] ) ) ) - 'Table'[D] )
Result:
By the way: On 1/110/2019, it should be -4420-0+1695=-2725 not -2825
here is pbix file, please try it.
Best Regards,
Lin
Thank you very much for your help @v-lili6-msft,
I tried your new calculated column and I'll tell you if it worked for me.
Talk to you later.
hi, @Raul
Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered?
Best Regards,
Lin
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 |
---|---|
114 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |