Price Index for Multiple Product Starting with 100

[NileshPatil3]

Hello, 

I am trying to create a price index in Power BI several times, but I am now unable to crack the code yet.  The price index to start with 100 for the minimum selected date. I have tried to create the index with the following code. However, it does not reflect the current value. 

Formula reference

Price Index =

VAR __BASELINE_VALUE =

    CALCULATE(

        SUM('Data'[Price]),

        'Date[Date] = Min('Date'[Date])

    )

VAR __MEASURE_VALUE = SUM('Date'[Price])

RETURN

    IF(NOT ISBLANK(__MEASURE_VALUE), (__MEASURE_VALUE/__BASELINE_VALUE)*100)

Here is a sample of data;

Date	Product	Price
Jan-18	BENZ	676
Jan-18	PHENL	556
Feb-18	PHENL	795
Feb-18	BENZ	663
Mar-18	PHENL	792
Mar-18	BENZ	778
Apr-18	BENZ	786
Apr-18	PHENL	753
May-18	BENZ	796
May-18	PHENL	701
Jun-18	PHENL	729
Jun-18	BENZ	698
Jul-18	BENZ	793
Jul-18	PHENL	762
Aug-18	BENZ	707
Aug-18	PHENL	607
Sep-18	BENZ	712
Sep-18	PHENL	675
Oct-18	PHENL	738
Oct-18	BENZ	654
Nov-18	BENZ	666
Nov-18	PHENL	666
Dec-18	PHENL	711
Dec-18	BENZ	606
Jan-19	PHENL	661
Jan-19	BENZ	612
Feb-19	PHENL	689
Feb-19	BENZ	678
Mar-19	PHENL	704
Mar-19	BENZ	644

Many thanks for your help,

Nilesh

[Mikelytics]

Hi @NileshPatil3 

Can you please try:

Price Index =

VAR __MIN_DATE = CALCULATE(MIN('Data'[Date]),ALL('Data'[Date])) 

VAR __BASELINE_VALUE =
    CALCULATE(
        SUM('Data'[Price]),
        'Date[Date] = __MIN_DATE
    )
VAR __MEASURE_VALUE = SUM('Data'[Price])
RETURN
    IF(NOT ISBLANK(__MEASURE_VALUE), (__MEASURE_VALUE/__BASELINE_VALUE)*100)

If this does not work please can you add the expected outcome you need in the end?

But please be aware that the result only can make sense when also the product column is used. Otherwise it will take the sum of the prices which will lead to wrong values. So it would be better to work with SUMX and aggregating on Date and Product level.

Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.

[NileshPatil3]

Hi Michael, Thanks a lot, much appreciated your help. 

I am getting the following outcome. 

I am looking for an outcome, where, if I select a product and use a date slicer it should provide me a base-level index from the selected date starting from 100. For now, if I select a date newer than 01/01/2018 the price index is calculated from 01/01/2028 instead of the selected month. Please see below result;

It would be a great help if I can get some guidance.

Also, it is possible to get results on a monthly basis instead of a date hierarchy.