Calculate delta between 2 rows and multiply by another cell

[carinatina_pbi]

Dear all,

I am trying to replicate this table below in PBI, in order to get the last 2 reds columns (S and T):

I could calculate the deltas columns but the result is in a different line as below:
(All cells highlighted in yellow should be in the first line in order to multiply by column Amount = - 1779.28)

I am using the formula below for Delta calculation increasing index:

There is anyway to calculate it in PBI?

Here you can find my PBI file:

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

Thanks in advance.

KR,

Carina

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[parry2k]

@carinatina_pbi Indeed, that should be an easy fix since the core of logic is in place. I will take a look when I get a chance.

[parry2k]

@carinatina_pbi this is a very challenging and interesting problem to solve. Although you already have a solution i have a crack at it and made it more dynamic with fewer measures and it allows to scale the solution easily (if required).

See the attached pbix file and all the measures are in the Number of Months table. This table is the core of the calculation and makes everything dynamic, currently, you have 5 columns but if you want you can grow this to as many as your want, just change the Number of Months table.

The result of measures is on Page 2. There is one measure called New Total Sum PM which I'm not very happy with but it works, at some point in time I will get back to this and improve on it 

Give it a shot and let me know how it goes?

Cheers!!

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[carinatina_pbi]

Hello @parry2k 

Thanks for the dynamic solution but it works only if I have one ticket.

If I have more than one this is not working as below:

Here you can find a file with more than one ticket.

If you have any solution for that, it will help me a lot.

KR,

Carina

[v-yalanwu-msft · Accepted solution]

Hi, @carinatina_pbi ;

You could create  measures as  follows:

Col 1 = MAX('Ticket'[Amount])*CALCULATE(MAX('Exchange rate'[Delta 1]),FILTER(ALL('Ticket'),EOMONTH([Period Month],0)=EOMONTH(MAX('Ticket'[Period Month]),1)))

Col 2 = MAX('Ticket'[Amount])*CALCULATE(MAX('Exchange rate'[Delta 2]),FILTER(ALL('Ticket'),EOMONTH([Period Month],0)=EOMONTH(MAX('Ticket'[Period Month]),2)))

Col 3 = MAX('Ticket'[Amount])*CALCULATE(MAX('Exchange rate'[Delta 3]),FILTER(ALL('Ticket'),EOMONTH([Period Month],0)=EOMONTH(MAX('Ticket'[Period Month]),3)))

Col 4 = MAX('Ticket'[Amount])*CALCULATE(MAX('Exchange rate'[Delta 4]),FILTER(ALL('Ticket'),EOMONTH([Period Month],0)=EOMONTH(MAX('Ticket'[Period Month]),4)))

Col 4 = MAX('Ticket'[Amount])*CALCULATE(MAX('Exchange rate'[Delta 4]),FILTER(ALL('Ticket'),EOMONTH([Period Month],0)=EOMONTH(MAX('Ticket'[Period Month]),4)))

total sum = MAXX(FILTER(ALL('Ticket'),EOMONTH([Period Month],0)=EOMONTH(MAX('Ticket'[Period Month]),-1)),[Col 1])+MAXX(FILTER(ALL('Ticket'),EOMONTH([Period Month],0)=EOMONTH(MAX('Ticket'[Period Month]),-2)),[Col 2])+MAXX(FILTER(ALL('Ticket'),EOMONTH([Period Month],0)=EOMONTH(MAX('Ticket'[Period Month]),-3)),[Col 3])+MAXX(FILTER(ALL('Ticket'),EOMONTH([Period Month],0)=EOMONTH(MAX('Ticket'[Period Month]),-4)),[Col 4])+MAXX(FILTER(ALL('Ticket'),EOMONTH([Period Month],0)=EOMONTH(MAX('Ticket'[Period Month]),-5)),[Col 5])

Real Dif.Monthly = [Monthly Diff.]-([total sum]-SUMX(FILTER(ALL(Ticket),EOMONTH([Period Month],0)=EOMONTH(MAX('Ticket'[Period Month]),-1)),[total sum]))

Real Dif.Monthly = [Monthly Diff.]-([total sum]-SUMX(FILTER(ALL(Ticket),EOMONTH([Period Month],0)=EOMONTH(MAX('Ticket'[Period Month]),-1)),[total sum]))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[carinatina_pbi]

Hello @v-yalanwu-msft 

Thanks for this solution. It works but only I have only one ticket filtered into the file:

If I have 2 tickets, I got wrongs number into Col columns, then the others columns based on that is also wrong. Do you see any solution for that?

Here you will find the new PBI file with 2 tickets.

Thanks in advance.

KR,

Carina

[Ashish_Mathur]

Hi,

I would like to try.  Please share your MS Excel file with all workings/formulas so that i can understand those and convert them to DAX expressions.

[carinatina_pbi]

In this link you will find the excel with all formulas in tab Results:

https://docs.google.com/spreadsheets/d/1Rnlu5_dMAdLn4Tj7vF5WkWiojG2t_Qw3/edit?usp=sharing&ouid=11713...

Thanks for your help.

KR,

Carina

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[carinatina_pbi]

Thanks for you help, @Ashish_Mathur !

Your solution worked perfect.

[Ashish_Mathur]

You are welcome.

[carinatina_pbi]

@Ashish_Mathur 

Do you see any other way to calculate my target columns below, without creating several columns (Delta's and Col's)? Because I can have tickets with 30 lines, then based on this proposal solution I will need to create 30 Delta's columns and also 30 Col's columns, and I tried with 10 and PBI is running for a log time calculating it.

Thanks for your help again in advance.

KR,

Carina

[Ashish_Mathur]

Hi,

No, I cannot think of another way.

[HotChilli]

Let's fix delta1.

You want :

next month's exch rate - this month's exch. rate

so swap the things being subtracted (a-b should be b-a) and where the code adds 1 (+1) to the index it should be subtracting (-1)

-----

That should give you enough information to fix it.  Let me know how you get on.

[carinatina_pbi]

Thanks for your reply and help.

In fact, now I can see all values in the first line as I need to calculate next colum (amount * delta n), but when I create the second multiply column I got this message below:

To add, there is an automatically way to calculate as a loop?
Otherwise I will need to create like 30 lines for delta and after more 30 for the multiplication, as this is just a example, but others Tickets can have more periods/lines.

The target column needed in the end, is the Real Dif. Monthly that is calculating the formula below:

Real Dif. Monthly = (Amount 2 - Amount * Exc. Rate) - (sum of (Amount * Delta n) - line by line).

I upload my excel file with all formulas into this link (tab results) maybe this is easier to understanding:

https://docs.google.com/spreadsheets/d/1Rnlu5_dMAdLn4Tj7vF5WkWiojG2t_Qw3/edit#gid=411313650

Thanks in advance.

KR,

Carina