Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 ( 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?
 

 

Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

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

  • mahoneypat's avatar
    mahoneypat
    Microsoft Employee
    5 years ago

    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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Awesome, much appreciated!