cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Using slicer to change dates for calculating difference column

Hi all,

 

I'm at a loss here and would love some help.

 

data.PNG

 

 

This is what my data looks like. The depth column increases by 0.5, which is why I created the groups column. Another example of the data below. 

 

data2.png

 

My goal is to have a graph similar to the one below, but one which shows the temperature difference based on the date input from the slicer/slider. 

 

graph.png

 

For example if the slider is set from 7/10/2018 to 14/10/2018, the temperature difference at a depth of 20m would be given as 106.738 - 105.259 = 1.479. This calculation after being replicated for the other depths would then be plotted in their groups similar to the above picture.

 

Can this be done? I have tried many DAX statements to create columns but even then they are static and cannot be changed via a slicer.

 

Cheers,

Visura

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

How many data points are there for each depth and time?  Assuming only row for each combination, the following code shoud work.  Note that this is a measure, not a calculated column, so it will respond to a slicer.

 

Delta =
VAR MaxDate =
    MAX ( Table[Date] )
VAR MinDate =
    MIN ( Table[Date] )
VAR MinTemp =
    CALCULATE (
        MIN ( Table[Temperature] ),
        Table[Date] = MinDate
    )
VAR MaxTemp =
    CALCULATE (
        MAX ( Table[Temperature] ),
        Table[Date] = MaxDate
    )
RETURN
    MaxTemp - MinTemp

View solution in original post

2 REPLIES 2
Anonymous
Not applicable

How many data points are there for each depth and time?  Assuming only row for each combination, the following code shoud work.  Note that this is a measure, not a calculated column, so it will respond to a slicer.

 

Delta =
VAR MaxDate =
    MAX ( Table[Date] )
VAR MinDate =
    MIN ( Table[Date] )
VAR MinTemp =
    CALCULATE (
        MIN ( Table[Temperature] ),
        Table[Date] = MinDate
    )
VAR MaxTemp =
    CALCULATE (
        MAX ( Table[Temperature] ),
        Table[Date] = MaxDate
    )
RETURN
    MaxTemp - MinTemp
Anonymous
Not applicable

Thanks so much! Works perfectly.

 

I had been struggling for hours, and didn't think that a measure could dynamically hold values like this. Big props to you Chris. 

 

For the current data set there is only one temperature data point for each depth for a given day.  In future the frequency of the data will increase, from once/day -> once/15mins. From what I understand of this code, this approach will still work given the higher resolution, correct?

Helpful resources

Announcements
Join Arun Ulag at MPPC23

Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors