cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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:

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

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

 

View solution in original post

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. Smiley Happy

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.

 

Example.PNG

Anonymous
Not applicable

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

Link to PBIX

Anonymous
Not applicable

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

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.