cancel
Showing results for
Did you mean:
Regular Visitor

## Using unfiltered data in a line chart

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 ( 20191231 ) )

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?

1 ACCEPTED SOLUTION
Super User IV

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

4 REPLIES 4
Super User IV

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Regular Visitor

Awesome, much appreciated!

Super User IV

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

Proud to be a Super User!

Regular Visitor

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

Announcements