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

lc_finance's avatar
lc_finance
Icon for Solution Sage rankSolution Sage
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!