Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 dif...
Need Help
  • lc_finance's avatar
    lc_finance
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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,

lc_finance's avatar
lc_finance
Icon for Solution Sage rankSolution Sage
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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!

     

    • lc_finance's avatar
      lc_finance
      Icon for Solution Sage rankSolution Sage
      6 years ago

      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