Forum Discussion

powerBAMbi's avatar
powerBAMbi
New Member
3 years ago

Relative difference plot of time series data when sliced by different dates

Hello, I am attempting to create a relative difference plot of time series data, but based on the reading of the current date in the date slicer on a page. Using Python, it is relatively simple as both the dates:

 

df=dataset

x=df['date'] 

y=df['y']-df['y'][0]

In PowerBi, I get close when using a Difference from Filtered Value Quick measure with the following code:

 

Difference_Value =
VAR __BASELINE_VALUE =
    CALCULATE(
        MAX('Y_Values'[Value]),
        'Value'[tm_stamp]
            IN { (DATE(2023, 2, 11) + TIME(4, 0, 0)) }
    )
VAR __MEASURE_VALUE = MAX('Y_Values'[Value])
RETURN
    IF(NOT ISBLANK(__MEASURE_VALUE), __MEASURE_VALUE - __BASELINE_VALUE)
 
The problem being I have to select a particular date to perform the Quick Measure from, whereas I would like it to be flexible to use the min date of the current slicer.
 
One thought is to change the IN(DATE) to be the Min date of the slicer, but perhaps there is a more eloquant solution.
 
Much appreciated for any help
Need Help
Tips and Tricks

3 Replies

  • powerBAMbi's avatar
    powerBAMbi
    New Member
    3 years ago

    Thank you for the help! I have gotten very close to implementation of this solution. However, I am stuck on this code:

     

    ALLSELECTED('Date'[Date])

     

    Uncertain on how to reference a date slicer. I believe you referenced it here: create a measure that calculates the minimum date selected in the slicer.

     

    Many thanks again

    • Sahir_Maharaj's avatar
      Sahir_Maharaj
      Icon for Super User rankSuper User
      3 years ago

      Hello powerBAMbi,

       

      The ALLSELECTED('Date'[Date]) function is used to reference the date slicer in the measure.

       

      To reference a date slicer in Power BI, you need to replace Date in the expression ALLSELECTED('Date'[Date]) with the name of the date column that you're using in the slicer.

       

      Min_Date_Selected = MINX(FILTER('Y_Values', ALLSELECTED('Y_Values'[tm_stamp])), 'Y_Values'[tm_stamp])

       

      This measure uses the ALLSELECTED function to reference the date slicer and filter the dataset based on the selected date range. It then uses the FILTER function to apply the filter to the tm_stamp column in the Y_Values table.

       

      The MINX function is used to return the minimum date value from the filtered dataset. This value is assigned to the Min_Date_Selected variable.

       

      With this measure, you can calculate the minimum date value selected in the date slicer and use it in your difference measure to calculate the baseline value.

       

      Let me know if you might need further guidance.

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Icon for Super User rankSuper User
    3 years ago

    Hello powerBAMbi,

     

    One approach to achieve the desired result is to create a measure that calculates the minimum date selected in the slicer, and then use that measure as a filter in the DAX formula.

     

    Difference_Value_Relative =
VAR __MIN_DATE =
    CALCULATE(
        MIN('Y_Values'[tm_stamp]),
        ALL('Y_Values'),
        ALLSELECTED('Date'[Date])
    )
VAR __BASELINE_VALUE =
    CALCULATE(
        MAX('Y_Values'[Value]),
        'Y_Values'[tm_stamp] = __MIN_DATE
    )
VAR __MEASURE_VALUE = MAX('Y_Values'[Value])
RETURN
    IF(NOT ISBLANK(__MEASURE_VALUE), __MEASURE_VALUE - __BASELINE_VALUE)

     

    This formula should give you the relative difference plot of time series data based on the current date in the date slicer.

     

    Let me know if you might need further guidance.