Values between 2 dates

[krzysj13]

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.

[v-alq-msft]

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.

[DataInsights · Accepted solution]

@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

[krzysj13]

Hey, thanks for the answer, but I have one more question, what if I have 2 alarms in 1 period of time?

[v-alq-msft]

Hi, @krzysj13 

It depends on the expected result. You can choose to calculate the  aggregation of the multiple alarms for a specific period. In my dax provided above, I calculated the maximum of alarms for a period. If you want to use other aggregations, you may modify it as what you need.

Best Regards

Allan

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