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.
I have this table:
ID | ClientID | Date(dd/mm/yyyy)| Amount
-----------------------------------------------
6 | 1 | 01/01/2021 | 100
8 | 1 | 01/01/2021 | 150
3 | 1 | 02/01/2021 | 50
1 | 1 | 09/01/2021 | 200
5 | 2 | 03/01/2021 | 100
4 | 2 | 06/01/2021 | 50
7 | 2 | 07/01/2021 | 100
3 | 2 | 07/01/2021 | 50
9 | 2 | 07/01/2021 | 200
I need a running total base on the ClientID and the date but if there is a duplicate date I want to Sum the smaller ID.
Expected result:
ID | ClientID | Date(dd/mm/yyyy)| Amount |Running Sum
-------------------------------------------------
6 | 1 | 01/01/2021 | 100 | 100
8 | 1 | 01/01/2021 | 150 | 250
3 | 1 | 02/01/2021 | 50 | 300
1 | 1 | 09/01/2021 | 200 | 500
5 | 2 | 03/01/2021 | 100 | 100
4 | 2 | 06/01/2021 | 50 | 150
7 | 2 | 07/01/2021 | 100 | 300
3 | 2 | 07/01/2021 | 50 | 200
9 | 2 | 07/01/2021 | 200 | 500
I tried somthing like that without any luck:
RunningTotal =
var ClientIDValue = TBL[ClientID]
var DateValue = TBL[Date]
var FilterTbl = FILTER(TBL,TBL[ClientID] = ClientIDValue && TBL[Date] <= DateValue)
RETURN
CALCULATE(
sum(TBL[Amount])
,FilteredTbl
)
Solved! Go to Solution.
Hi @xl0911 ,
On the query editor sort you data by:
Then add and index column now do your column in the following way:
CUMULATIVE =
CALCULATE (
SUM ( 'Table'[ Amount] ),
FILTER (
ALL ( 'Table' ),
'Table'[ ClientID ] = EARLIER ( 'Table'[ ClientID ] )
&& 'Table'[ Date(dd/mm/yyyy)] <= EARLIER ( 'Table'[ Date(dd/mm/yyyy)] )
&& 'Table'[Index] >= EARLIER ( 'Table'[Index] )
)
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @xl0911 ,
On the query editor sort you data by:
Then add and index column now do your column in the following way:
CUMULATIVE =
CALCULATE (
SUM ( 'Table'[ Amount] ),
FILTER (
ALL ( 'Table' ),
'Table'[ ClientID ] = EARLIER ( 'Table'[ ClientID ] )
&& 'Table'[ Date(dd/mm/yyyy)] <= EARLIER ( 'Table'[ Date(dd/mm/yyyy)] )
&& 'Table'[Index] >= EARLIER ( 'Table'[Index] )
)
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsIs it possible to do it without the query editor, only with pure DAX ?
Hi @xl0911 ,
Since your value is based on dates and ID try the following:
Ranking = RANKX('Table', FORMAT('Table'[ Date(dd/mm/yyyy)], "###")*10000 + 'Table'[ID ], ,ASC)
Be aware that depeding on the total value of your ID you may need to multiply by more that 10000 in order that your calculation gets the correct maximum values.
CUMULATIVE =
CALCULATE (
SUM ( 'Table'[ Amount ]),
FILTER (
ALL('Table'),
'Table'[ ClientID ] = EARLIER ( 'Table'[ ClientID ] )
&& 'Table'[Ranking] <= EARLIER ( 'Table'[Ranking] )
)
)
Result below:
Has you can see the column CUMULATIVE is equal to the Running Sum that I added from your table to show comparision.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |