Indexcolum or not?

[Anonymous]

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:

Period	EURO €
201801	2808
201801	3604
201802	3744
201802	7175
201802	403
201803	463
201803	74888
201804	2808
201804	2148
201804	6288
201805	2286
201805	2543
201806	1404
201806	68184
201807	12795
201807	3978
201810	468
201811	8679
201811	19656
201812	1872
201812	7175
201812	26208
201812	87912
201901	7175
201901	3096
201902	3096
201902	3096
201902	3754
201903	504
201905	42
201907	516
201907	2064
201908	4128

Periodtable(full)

Index PERIOD_NR

1	201801
2	201802
3	201803
4	201804
5	201805
6	201806
7	201807
8	201808
9	201809
10	201810
11	201811
12	201812
13	201813
14	201901
15	201902
16	201903
17	201904
18	201905
19	201906
20	201907
21	201908
22	201909
23	201910
24	201911
25	201912
26	201913
27	202001

[Anonymous]

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]

@Anonymous 

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

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]

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]

@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 · Accepted solution]

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