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

Reply
sandeo
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
mahoneypat
Microsoft Employee
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


View solution in original post

4 REPLIES 4
mahoneypat
Microsoft Employee
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


Awesome, much appreciated!

amitchandak
Super User
Super User

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

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

 

Sample.PNG

 

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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