cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## How to create a delta calculation table from columns in another table

Hi everyone,

I have a table which structure looks like this:

Columns (A to Z....).  Each day, a new column is automatically created from data in the cloud.

How can I calculate in a different table, the deltas between the columns in the table? e.g., (B-A), (C-B)... considering that each day a new column is  created in the original table?

Thank you,

1 ACCEPTED SOLUTION
Solution Sage

Hi @Anonymous ,

can you unpivot those columns in Power Query?

The goal is to have:

 Row Column Value 1 A 0 2 A 0 1 C 4

If you can have a table like this, then it's possible to calculate automatically the delta between A and B, B and C, etc

LC

Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com

5 REPLIES 5
Solution Sage

Hi @Anonymous ,

can you unpivot those columns in Power Query?

The goal is to have:

 Row Column Value 1 A 0 2 A 0 1 C 4

If you can have a table like this, then it's possible to calculate automatically the delta between A and B, B and C, etc

LC

Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com

Anonymous
Not applicable

Hi @lc_finance . Sorry for the late reply!

I was able to transpose the matrix. Adding some other variables, now it looks like this:

The idea is to calculate the deltas in each of the columns, for each one of the rows. Data will be added as a row from the cloud everyday.

For example:

1/1/2019                1/2/2019 ......            12/31/2019

11/26/2019 - 11/25/2019

11/25/2019 - 11/24/2019

.

.

.

Do you know how can I do that in a new matrix? I would use the deltas for other calculations.

Thank you,

Solution Sage

Hi @Anonymous ,

Instead of transposing, can you try to unpivot it?

The goal is to have 3 columns

Row .                   Column               Value

If easier, can you share a sample Power BI file with your data and an example of what you want to obtain?

LC

Anonymous
Not applicable

Hi @lc_finance ,

Thanks for your reply. Please find attached a sample of the table that I have. New columns will be added every day from the cloud.

The idea is to calculate a secondary table, not visualization, of the deltas of the rows of primary table,  the result would be:

1/1/2019         1/10/2019 .....

1 row: d1 (10/5/2019 - 10/4/2019)         0                        0

2 row: d2 (10/6/2019  - 10/5/2019)        0                        0

.

.

I would like to calculate a new table,  so I can create new calculated columns based on the delta values.

Please let me know if you have any idea. Thank you!

Solution Sage

Hi @Anonymous ,

I created a new table (data 2) where I unpivoted the columns.

With the unpivot, the new table has 3 columns: stay date (same as you), date 2 (the unpivot of the columns), value (the values).

After that, I added a DAX formula for the difference.

The difference column is equal to the current value minus the prior value. The prior value is found based on the 'date 2' column.

Is this what you are looking for?

I hope this helps you!

LC

Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!