cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## 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:

 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
1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi @Anonymous

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 5
Anonymous
Not applicable

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
Not applicable

@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
Not applicable

Hi @Anonymous ,

I used these steps:

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
Not applicable

@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?

Anonymous
Not applicable

Hi @Anonymous

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors