Using unfiltered data in a line chart

[sandeo]

Hello guys,

I am struggling to find a solution, the situation is as following

There is a Date slicer where users can select a certain Date range, for example 31-12-2019 to 27-01-2021.

Every date there is a value "Value". Now, I want to calculate the percentage difference with the earliest value available in this date range (in this case on the 31-12-2019) and visualize the difference over time in a line chart. So the chart would always start at an index of 100, because the value is the same as the earliest value available. 

The following hard coded example works:

Test =
SUM ( Table[Value] )
    / CALCULATE ( SUM ( Table[Value] ), 'Calendar'[Date] = DATE ( 2019, 12, 31 ) )

The only thing is that Callendar[Date] should be flexible and dependant on the slicer selection, but I am struggling to get this working. I have tried the following:

EarliestDate = FIRSTDATE('Calendar'[Date])

Test2 =
SUM ( Table[Value] )
    / CALCULATE (
        SUM ( Table[Value] ),
        FILTER ( ALLSELECTED ( 'Calendar' ), 'Calendar'[Date] = [EarliestDate] )
    )

The chart looks almost to be in the right form, but the values seem off as the chart starts with an index of 0,15 instead of 100. If I do not use the ALLSELECTED or ALL function, I am getting the same index of 100 for all dates as EarliestDate is equal to a given date on the chart.

What is the best way around it?

[mahoneypat · Accepted solution]

Please try this measure to get your desired chart.

Test 3 =
VAR vMinDate =
    MINX (
        ALLSELECTED ( 'Calendar'[Date] ),
        'Calendar'[Date]
    )
VAR vMinValue =
    CALCULATE (
        SUM ( Facts[Value] ),
        'Calendar'[Date] = vMinDate
    )
RETURN
    DIVIDE (
        SUM ( Facts[Value] ),
        vMinValue
    )

Pat

[sandeo]

Awesome, much appreciated!

[amitchandak]

@sandeo ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[sandeo]

I created a sample .pbix file with just a few records and I have the same problem...

PBIX: https://we.tl/t-pUO9G9D3S6