Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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,
Solved! Go to Solution.
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
Does this help you?
LC
Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com
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
Does this help you?
LC
Interested in Power BI and DAX tutorials? Check out my blog at www.finance-bi.com
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,
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
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.
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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |