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

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