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, I am very new to Power BI Desktop and trying to write one DAX formula to calculate Cumulative charges value which can be use in Waterfall visualization. I can do this in Excel but not sure how can I make this happen in Power BI.
I have a list of clients with their Daily Charges and payment posted. I need to calculate Daily change (Highlighted in Green below).
For Client 1 Daily changes on 04th April would be Value of 3rd - Value of 4th April and daily changes on 05th April would be Value of 4th - Value of 5th April. Same for rest of the clients. Please help me with this, I am trying to figure this out past 2 weeks.
Solved! Go to Solution.
Hi @Anonymous,
Please modify the Rank column as below for a test.
Rank = RANKX ( FILTER ( 'Daily Cumulative Value', 'Daily Cumulative Value'[Practice] = EARLIER ( 'Daily Cumulative Value'[Practice] ) ), 'Daily Cumulative Value'[Report Date], , ASC, DENSE )
Best regards,
Yuliana Gu
Hi @Anonymous,
In this scenario, you should create two calculated columns to get the desired daily cumulative value.
Rank = RANKX ( 'Daily Cumulative Value', 'Daily Cumulative Value'[Report Date], , ASC, DENSE ) Daily charge = 'Daily Cumulative Value'[Charges] - LOOKUPVALUE ( 'Daily Cumulative Value'[Charges], 'Daily Cumulative Value'[Practice], 'Daily Cumulative Value'[Practice], 'Daily Cumulative Value'[Rank], 'Daily Cumulative Value'[Rank] - 1 )
Best regards,
Yuliana Gu
Hello Yuliana, Thank you so much for your help. Column for Rank is working fine but formula of Daily Charges flashing an error.
Formula I used:
Daily Charges =
'Financial'[Charges]
- LOOKUPVALUE(
'Financial'[Charges],
'Financial'[Practice], Financial[Practice],
Financial[Rank], 'Financial'[Rank] - 1
)
Getting following error : 'A table of multiple values was supplied where a single value was expected'.
Just to update you that there are more than 400 Client name in original raw report. Thanks
Hi @Anonymous,
In your source table, does this scenario exist? The same client ID has multiple data rows in a day?
Client1 April 03,2017
Client1 April 03,2017
Client2 April 03,2017
Regards,
Yuliana Gu
Hello @v-yulgu-msft, Every Client ID will have only one row in a day. No multiple entries for any client.
Hi @Anonymous,
Please modify the Rank column as below for a test.
Rank = RANKX ( FILTER ( 'Daily Cumulative Value', 'Daily Cumulative Value'[Practice] = EARLIER ( 'Daily Cumulative Value'[Practice] ) ), 'Daily Cumulative Value'[Report Date], , ASC, DENSE )
Best regards,
Yuliana Gu
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 |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |