Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Indexcolum or not?

Hello,   I want to calculate the difference between different periods, like a sum of year to year(13 periods), a sum of period(1 period versus previous period). I have 2 tables. 1 table with data a...
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi Anonymous 

     

    Please find my attached file.

     

    https://www.dropbox.com/s/x0bgw6swyq6jtmw/Question%20indexcolum.pbix?dl=0

     

    I have added an index column ( starting from 1) in Data Table.

    I have also added the new columns which has formulas.

     

    The cards Visuals shows the Variance of year Euro and % variance of the same.

     

     

    Let me know if this works for you.

     

    Thanks,

    Tejaswi

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Anonymous ,

I am little confuse with your requirements.

Can you show us the screenshot  of expected output you are looking for based on your sample data?

Thanks,

Tejaswi

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Anonymous 

Sorry for the making you confused. Its just my beginners skills. :smileyhappy:

Maybe i dont have to make an index table. i just want to analyse different periods against eachother and make up the difference in € and %.

 

I made some extra entries in th epicture of periods/values to make it more realistic.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous ,

     

    I used these steps:

    1- added an index column

    2. Calculated sumofmonths

    SumOfMonths = 
Var prev=CALCULATE(SUM(Table8[EURO €]),ALLexcept(Table8,Table8[Period]))
return
if([Index]>calculate(Min(table8[index]),filter(all(table8),Table8[Period]=earlier(Table8[Period]))),blank(),prev)

    3. Splitted the period column assuming it is a wholenumber datatype (based on your sample)

    Split col = left(Table8[Period],4)

    4. calculated SumOfyear

    SumOfYear = 
Var Prev1=CALCULATE(SUM(Table8[EURO €]),ALLexcept(Table8,Table8[Split col]))
return
if([Index]>calculate(Min(table8[index]),filter(all(table8),Table8[Split col]=earlier(Table8[Split col]))),blank(),Prev1)

    5. date diff between the years

    DateDiff Column = 
VAR PreviousDate =
    CALCULATE (
        LASTDATE ( Table8[Period]),
        
        Table8[Period] < EARLIER ( Table8[Period] )
    )
VAR CurrentDate = Table8[Period]
RETURN
    IF ( ISBLANK ( PreviousDate ), 0, DATEDIFF ( PreviousDate, CurrentDate, DAY ) )

    Let me know if these works.

     

    Thanks,

    Tejaswi

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Anonymous 

      Hi Tejaswi,

       

      Thanks for your reply. I tried the code but i get stuck at the firts line.

      im not sure what to do with the index. 

       

      Maybe you can take a look in my PBIX file?

      Link to PBIX

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi Anonymous 

         

        Please find my attached file.

         

        https://www.dropbox.com/s/x0bgw6swyq6jtmw/Question%20indexcolum.pbix?dl=0

         

        I have added an index column ( starting from 1) in Data Table.

        I have also added the new columns which has formulas.

         

        The cards Visuals shows the Variance of year Euro and % variance of the same.

         

         

        Let me know if this works for you.

         

        Thanks,

        Tejaswi