Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to Solution.
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
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
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
Best Regards,
Samarth
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Connect on Linkedin
@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?
User | Count |
---|---|
25 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
30 | |
13 | |
11 | |
9 | |
6 |