Index 100 Measure with dynamic 100 base date

[ncreixent]

Hello Everyone!

   I'm trying to create a measure that expresses prices as base 100, where the base 100 date is the earliest filtered date on a page level.

   The dataset that I have contains volume and revenue data from december 2015 to december 2017. My approach so far was the following:

Price_Index = 
VAR Date_index =
    CALCULATE(MIN( 'SSD'[DATE] ),ALL(SSD))
RETURN
    (SUM(SSD[Val])/sum(SSD[Vol]))/(CALCULATE ( SUM ( SSD[Val] ), SSD[DATE] = Date_index )/CALCULATE ( SUM ( SSD[Vol] ), SSD[DATE] = Date_index ))*100

   This gets the minimum date on the overall table, without any filters, and then calculates the current price (Value/Volume) divided by the price on the minimum date. This does provides an INDEX 100 for base December 2015, but what I want it to do is to calculate the base date from the minimum date on the plot, considering page level filters. That is, if I filter dates on a page level from December 2016 to December 2017, I want the base date to be december 2016. The problem is that when I remove the filters to get the Date_Index, it removes the page level filters as well.

   I don't know if its possible the create something like a measure constant on DAX, that before any row level calculations get the minimum date on the table that is been used for the plot.

   Do you have any ideas of how to work around this?

Thanks very much in advance!

[Zubair_Muhammad · Accepted solution]

HI @ncreixent

Try using ALLSELECTED instead of ALL

i.,e. to say

Price_Index =
VAR Date_index =
    CALCULATE ( MIN ( 'SSD'[DATE] ), ALLSELECTED ( SSD ) )
RETURN
    ( SUM ( SSD[Val] ) / SUM ( SSD[Vol] ) )
        / (
            CALCULATE ( SUM ( SSD[Val] ), SSD[DATE] = Date_index )
                / CALCULATE ( SUM ( SSD[Vol] ), SSD[DATE] = Date_index )
        )
        * 100

[ncreixent]

Worked perfectly. Thank you @Zubair_Muhammad!