cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sandeo
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
mahoneypat
Super User IV
Super User IV

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
Super User IV
Super User IV

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

Awesome, much appreciated!

amitchandak
Super User IV
Super User IV

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.