Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
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.png

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
lc_finance
Solution Sage
Solution Sage

Hi @Anonymous ,

 

 

can you unpivot those columns in Power Query?

The goal is to have:

 

RowColumnValue
1A0
2A0
1C4

 

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

 

Does this help you?

 

LC

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

View solution in original post

5 REPLIES 5
lc_finance
Solution Sage
Solution Sage

Hi @Anonymous ,

 

 

can you unpivot those columns in Power Query?

The goal is to have:

 

RowColumnValue
1A0
2A0
1C4

 

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

 

Does this help you?

 

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:

 

matrix2.PNG

 

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,

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.

 

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

 

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!

 

Hi @Anonymous ,

 

 

You can download my proposed solution from here.

 

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.

 

Find Difference.png

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.