Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
NileshPatil3
New Member

Price Index for Multiple Product Starting with 100

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;
 
DateProductPrice
Jan-18BENZ676
Jan-18PHENL556
Feb-18PHENL795
Feb-18BENZ663
Mar-18PHENL792
Mar-18BENZ778
Apr-18BENZ786
Apr-18PHENL753
May-18BENZ796
May-18PHENL701
Jun-18PHENL729
Jun-18BENZ698
Jul-18BENZ793
Jul-18PHENL762
Aug-18BENZ707
Aug-18PHENL607
Sep-18BENZ712
Sep-18PHENL675
Oct-18PHENL738
Oct-18BENZ654
Nov-18BENZ666
Nov-18PHENL666
Dec-18PHENL711
Dec-18BENZ606
Jan-19PHENL661
Jan-19BENZ612
Feb-19PHENL689
Feb-19BENZ678
Mar-19PHENL704
Mar-19BENZ644

 

Many thanks for your help,

 

Nilesh

2 REPLIES 2
Mikelytics
Resident Rockstar
Resident Rockstar

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)

Mikelytics_0-1674215276417.png

 

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.

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

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

 

I am getting the following outcome. 

NileshPatil3_0-1674451644331.png

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;

 

NileshPatil3_1-1674451929717.png

 

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. 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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