Creating Column/Measure depending on variables

Hello everyone,

It's not been a long time I'm using Power Bi and need your help to solve this issue.

I have the following data

ID	ID2	Date1	Date2
1	2	9/29/2021	10/30/2021
2	3	9/30/2021	10/30/2021
2	4	10/5/2021	10/30/2021
3	10	10/5/2021	11/15/2021
3	13	10/8/2021	11/15/2021
3	15	11/2/2021	11/15/2021
4	23	1/31/2021	5/1/2021
4	24	2/2/2021	5/1/2021

I want to create a column which will be equal to date2 but in specific time period - values that are more then min value of date1 and less than max value of date1.

To clarify, I tried with the formula below.

Time Period = 
VAR MIN_P_ = MIN('Tbl'[Date1])
VAR MAX_P_ = MAX('Tbl'[Date1])
VAR Time_Per = MIN_P_<='Tbl'[Date2]<= MAX_P_
return Time_Per

Just this does not work,it gives me this error: DAX comparison operations do not support comparing values of type True/False with values of type Date. Consider using the VALUE or FORMAT function to convert one of the values.

Could you please help to transform and adjust the formula above so that I could get a desired result?

Thank you in advance

Samarth_18

4 years ago

Hi Nini1 ,

You can try below code:-

Time Period =
VAR MIN_P_ =
    CALCULATE ( MIN ( 'Dates_range'[Date1] ), ALL ( Dates_range ) )
VAR MAX_P_ =
    CALCULATE ( MAX ( 'Dates_range'[Date1] ), ALL ( Dates_range ) )
VAR Time_Per =
    IF (
        AND ( 'Dates_range'[Date2] >= MIN_P_, 'Dates_range'[Date2] <= MAX_P_ ),
        Dates_range[Date2]
    )
RETURN
    Time_Per

Ouput:-

Thanks,

Samarth

Forum Discussion

Creating Column/Measure depending on variables

3 Replies

Related content

Circular Dependency on column from measure

Measure two columns

Circular dependency

Dax query dependency

dependent slicers