Compare a value in row with a measure defining start date of selected time period

[Kristianhk]

Hi All

I have a date table. from that I have a slicer defining a time period

then I have two Measures

• timePeriod_START = CALCULATE(  MIN( DateTable[Date] ) )
• timePeriod_END = CALCULATE(  MAX( DateTable[Date] ) )

Then I have this Timeline Table 

MID	Status	Start Time	End Time
CNC 28	Production	01-01-2020 09:00:00	01-01-2020 23:00:00
CNC 28	stopped	01-01-2020 23:00:00	02-01-2020 09:00:00
CNC 28	alarm	02-01-2020 09:00:00	02-01-2020 23:00:00
CNC 28	stopped	02-01-2020 23:00:00	03-01-2020 09:00:00

I would like to have two new measures:

• NewStartTime = Maximum of  Timeline[Start time] and [TimePeriod_START]
• NewEndTime = Minimum of  Timeline[End time] and [TimePeriod_END]

I have the following challenges with the two measures:

1. I have trouble to find a way/functions which can give me a MAX/MIN of a row value and a measure. I have tried with MAXX without any luck, but the issue could be due to the next challenge mentioned below.
2. the [TimePeriod_START] and [TimePeriod_END] measures is somehow not fixed to the selected time period but is calculated by row,

the measure will be used in a "as timeline" Visual to make timelines for my machines. the measures will make sure start and end for each machine is the same in the visual so it looks pretty

Any device to finalice the measures would be much appreciated

/Kristian

[lbendlin]

1. is easy to resolve. Just use some form of meaningless aggregation against your row value

measure = max (max(Timeline[Start time]),Timeperiod_START)

etc.