Forum Discussion

krzysj13's avatar
krzysj13
Regular Visitor
5 years ago
Solved

Values between 2 dates

Hello, I'm looking the way to make new column in left table, with value from right column 'AlarmID', if its between 2 dates - LOCALTIME and Previous. Thanks alot in advance for help.    
  • DataInsights's avatar
    DataInsights
    5 years ago

    krzysj13, try this calculated column in the left table:

     

    Alarm ID =
VAR vAlarms =
    FILTER (
        Alarms,
        Alarms[Start Time] > Main[Previous]
            && Alarms[Start Time] <= Main[Local Time]
    )
VAR vResult =
    MAXX ( vAlarms, Alarms[Alarm ID] )
RETURN
    vResult

     

    Left table: Main

    Right table: Alarms

v-alq-msft's avatar
v-alq-msft
Community Support
5 years ago

Hi, krzysj13 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table1:

 

Table2:

 

You may create a measure or a calculated column as below.

Measure:

Measure = 
CALCULATE(
    MAX(Table2[AlarmId]),
    FILTER(
        ALL(Table2),
        [StartTime]>=SELECTEDVALUE(Table1[Pervious])&&
        [StartTime]<=SELECTEDVALUE(Table1[Local Time])
    )
)

 

Calculated column:

Column = 
CALCULATE(
    MAX(Table2[AlarmId]),
    FILTER(
        ALL(Table2),
        [StartTime]>=EARLIER(Table1[Pervious])&&
        [StartTime]<=EARLIER(Table1[Local Time])
    )
)

 

Result:

 

Best Regards

Allan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.