DAX for Running Savings

[hiba_aziz]

I'm working with two tables: Table1 and Table2 which they have many to many relationship on ID. 

Table2 contains the list of actions taken on a smaller subset of IDs from Table1 to reduce their cost. Table 2 has these fields: ActionDate, ID, Result.

Table1 has these fields: Date, ID, cost,..etc 

My task is to calculate the total savings from each Action. Where for each action we check the cost of the day before the action (CostAB) and if the cost of the day action is lower than the CostAB we compute the difference for that day and all the days until the next action date. 

For Example:

Action taken on the 1/10/2023, CostAB= 50 , NextActionDate = 1/13/2023
Saving for the 1/10/2023 = 50 - 0 = 50
saving for the 1/11/2023 = 50 - 30 = 20
saving for 1/12/2023       = 50 - 40 = 10 (we stop here because the next Action date is 1/13/2023)
Total savings for Action taken on the 10th would be = 50 + 20 +10 = 80 

Note: if there is no NextActionDate, the calculation will continue until the cost becomes higher than the cost before the last action taken. 
I'm unable to share my file since I'm connected to a live database, any helo with the dax would be much appreciated.
 

[Daniel29195 · Accepted solution]

Hello @hiba_aziz 

check the link below for the power bi file .

https://drive.google.com/file/d/1NK1Ub9VR2XathhaZKKtuHKhsRQxhSgwp/view?usp=sharing 

If my answer helped sort things out for you, feel free to give it a thumbs up and mark it as the solution! It makes a difference and might help someone else too. Thanks for spreading the good vibes! 👍🤠

[Daniel29195 · Accepted solution]

Hello @hiba_aziz 

check the link below for the power bi file .

https://drive.google.com/file/d/1NK1Ub9VR2XathhaZKKtuHKhsRQxhSgwp/view?usp=sharing 

If my answer helped sort things out for you, feel free to give it a thumbs up and mark it as the solution! It makes a difference and might help someone else too. Thanks for spreading the good vibes! 👍🤠

[Daniel29195 · Accepted solution]

@hiba_aziz 

in the code , 

just modify to this on line : 64

[hiba_aziz]

@Daniel29195    @Fowmy  @Ritaf1983 @Ahmedx @amitchandak 

[Daniel29195]

Hello @hiba_aziz  
output  : 

kindly download the pbix file from here. 

https://drive.google.com/file/d/1as7AW4DFwVFUN4LdpTgS-0A07Z1nDa4Q/view?usp=sharing

let me know if it works for you . 

NB : 
1.create dimdate 

2.create dimusers

3. link dimdate to both tables on date

4. link dimusers to both tables on user .

If my answer helped sort things out for you, feel free to give it a thumbs up and mark it as the solution! It makes a difference and might help someone else too. Thanks for spreading the good vibes! 👍🤠

[hiba_aziz]

Hey Daniel, 
your dax is not returning the expected result.

For example, the correct result for 1/1/2023 is 15: which is 30 -15. and for 1/2/2023 is 5. 

[hiba_aziz]

@Daniel29195 

Thanks for the quick response:

so the logic goes like this:

for each action date x:

1. we get the cost of the day before 

2. then we iterate through all dates starting from date x and we stop at the next action date: saving =  cost of the day before - cost of the date. 

3. aggregate the savings.

For date 1/1/2023:

Action date = 1/1/2023 , costAB = 30 , NextActionDate = 1/2/2023

saving = 30 - 15 = 15

we only compute savings for 1/1/2023, since the following date is an Action date.

[Daniel29195]

@hiba_aziz 

is this the output you want ? 

[hiba_aziz]

Given the table on the right, the result should be the small table on the left:

[Ashish_Mathur]

Hi,

In what form do you want to see the end result.  In a table format/card visual?  Please show that clearly.

[hiba_aziz]

Table format

[Ashish_Mathur]

so if 1/10/2023 is selected, then you would want to show one row for each date till 1/12/2023?  Am i correct?

[hiba_aziz]

you're right! you continue until the Next Action date, which is 1/13 in this case. 

when computing saving for 1/13/2023, the case is a bit different. where there is no Next Action, in this case you compute the saving until the end of the table or whenever the cost is higher than 40. 

[Ashish_Mathur]

Hi,

I am not sure how much i can help but i would like to try.  Share the 3 tables (2 input tables and 1 output table) in format that can be pasted in an MS Excel file.

[Daniel29195]

@hiba_aziz 

fix this in the code : 

replace it with : < 

for the second part : 

Note: if there is no NextActionDate, the calculation will continue until the cost becomes higher than the cost before the last action taken. 

can you please provide an example ? 

best regards

[hiba_aziz]

Yes, here is an example where the last action taken on x was 1/13/2023.

Then in order to compute the savings from that action, we need to look at all dates starting from that action until the end of table. 

For Action taken on 1/13/2023, cost of day before = 40:

saving for 1/13/2023 is = 40 - 30 = 10 

saving for 1/14/2023 is = 40 - 20 = 20

....

saving for 1/21/2023 is = 40 - 20 = 20

total saving for action taken on 1/13/2023 is = 10 + 20+ ....+20 = 170