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 and 1 with periods. in the periods table i have added a index column.

 

Can i calculate with my periods or do i have to continue with my indexcolumn idea?

and ofcourse how can i realise my goal?

 

I thought of adding the indexnumbers to the datatable and then calculate with SUM([EURO]), but i dont know how to do that.

 

 

Datatable extract:

PeriodEURO €
2018012808
2018013604
2018023744
2018027175
201802403
201803463
20180374888
2018042808
2018042148
2018046288
2018052286
2018052543
2018061404
20180668184
20180712795
2018073978
201810468
2018118679
20181119656
2018121872
2018127175
20181226208
20181287912
2019017175
2019013096
2019023096
2019023096
2019023754
201903504
20190542
201907516
2019072064
2019084128

 

Periodtable(full)

 

Index PERIOD_NR

1201801
2201802
3201803
4201804
5201805
6201806
7201807
8201808
9201809
10201810
11201811
12201812
13201813
14201901
15201902
16201903
17201904
18201905
19201906
20201907
21201908
22201909
23201910
24201911
25201912
26201913
27202001
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

     

5 Replies

  • 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