How to calculate index based on a base year (=100)

[Ptolemaida]

I have a table with revenues per shop per month. the table is connected to a calender table.

I want now to create a measure that calculates:

2020 = 100 (base year)

2021 = for ex 105, 2022 = 99, 2023 = 114, etc

so for each shop, calculate the difference with the base year , per year, where for each shop 2020 = 100

thank you

[amitchandak · Accepted solution]

@Ptolemaida ,  you can use this measure and take diff with revenue measure 

Calculate([Revenue], Date[Year] = 2000) 

or

Calculate([Revenue], Filter(all(Date), Date[Year] = 2000) )

[v-pnaroju-msft]

Hi Ptolemaida,

We are following up to see if what we shared solved your issue. If you need more support, please reach out to the Microsoft Fabric community.

Thank you.

[v-pnaroju-msft]

Thankyou, @amitchandak, @Amar_Kumar and @Ashish_Mathur for your responses.

Hi Ptolemaida,

We appreciate your inquiry through the Microsoft Fabric Community Forum.

We would like to inquire whether have you got the chance to check the solutions provided by @amitchandak, @Amar_Kumar and @Ashish_Mathur to resolve the issue. We hope the information provided helps to clear the query. Should you have any further queries, kindly feel free to contact the Microsoft Fabric community.

Thank you.

[Ashish_Mathur]

Hi,

Try this measure pattern

Total = sum(Data[sales])

Base = calculate([Total],calendar[year]=2000)

Calibrated to base = divide(100,[Base])*[Total]

If this does not help, then share some data to work with and show the expected result.

[Amar_Kumar · Accepted solution]

@Ptolemaida you can use below measure

Revenue Index =
VAR BaseYearRevenue =
CALCULATE (
SUM ( Revenue[Amount] ),
ALL ( 'Calendar'[Year] ),
'Calendar'[Year] = 2020
)

VAR CurrentYearRevenue =
SUM ( Revenue[Amount] )

RETURN
IF (
NOT ISBLANK ( BaseYearRevenue ),
DIVIDE ( CurrentYearRevenue, BaseYearRevenue, 0 ) * 100
)

Hope this helps, if it does please mark it as a solution.

[amitchandak · Accepted solution]

@Ptolemaida ,  you can use this measure and take diff with revenue measure 

Calculate([Revenue], Date[Year] = 2000) 

or

Calculate([Revenue], Filter(all(Date), Date[Year] = 2000) )