topic Re: Creating Column/Measure depending on variables in DAX Commands and Tips

Creating Column/Measure depending on variables

Nini1 — Mon, 10 Jan 2022 14:14:04 GMT

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

Re: Creating Column/Measure depending on variables

Samarth_18 — Mon, 10 Jan 2022 14:25:17 GMT

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

Re: Creating Column/Measure depending on variables

Nini1 — Mon, 10 Jan 2022 14:54:14 GMT

Samarth_18 Thank you very much and should I change it when e.g. Date2 is from another table? I should write the code differently, right?

Re: Creating Column/Measure depending on variables

Samarth_18 — Mon, 10 Jan 2022 15:00:39 GMT

Nini1 I think it should work in that case as well.