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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent 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
Microsoft Employee

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

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

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

4 REPLIES 4
Microsoft Employee

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

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

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.

@mahoneypa HoosierBI on YouTube

Frequent Visitor

Awesome, much appreciated!

Super User

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

Frequent Visitor

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

## Helpful resources

Announcements

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors