Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi,
This is my table:
ID | Status | Date1 | Date2 |
1 | 1 | 2020-01-21 22:45:32 | 2020-02-01 12:00:00 |
2 | 1 | 2020-01-15 16:25:58 | 2020-02-03 15:45:00 |
3 | 0 | 2020-02-02 02:13:22 | 2020-02-01 04:00:00 |
4 | 1 | 2020-02-03 04:52:12 | 2020-02-01 19:15:00 |
And this is my measure:
Measure =
CALCULATE(
DISTINCTCOUNT(Table[ID]);
FILTER(
Table;
Table[Status] = 1
&&
Table[Date2] <= NOW()
&&
Table[Date2] >= Table[Date1]
)
) + 0
The problem is it works fine only for certain dates. Record of ID equal to 1 works fine but record of ID 2 doesn't. In general dates before 15th January 2020 doesn't work at all. When I change my measure to
Measure =
CALCULATE(
DISTINCTCOUNT(Table[ID]);
FILTER(
Table;
Table[Date2] >= Table[Date1]
)
) + 0
it returns true (1 as value) only when Date1 is in between 16th January and now. How can I fix it?
I can't find any information on how to compare dates from two columns from the same table. Is my operator wrong?
Hi @Anonymous ,
I don't really understand what you are trying to achieve. You say "Record of ID equal to 1 works fine but record of ID 2 doesn't." which indicated that you are trying to evaluate every row. Why are you using a measure and not a calculated column?
If you expected the value of your measure to be 2, check the following:
1. are your columns of type DateTime?
2. wrap Table in an ALL statmement in your Filter: ...FILTER(ALL(Table),....
Did that resolve your issue?
Thanks!
Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
Proud to be a Super User!
The formula seems fine. Just check if the data types of the dates time column has been detected as DateTime or not.
Yes, both of my columns are correctly recognized as DateTime columns, what's more both have the same format.
Hi @Anonymous ,
Could you tell me if your problem has been solved? If it is, kindly mark the helpful answer as a solution if you feel that makes sense. Welcome to share your own solution. More people will benefit from here.
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
No, in my report I had to create a little workaround building three DAX measures. In one of them I compared dates with '>' and '<' operators and it did work. Perhaps a bug in my measure occured and we all missed it. Anyway I can't point any good solution to this issue. I hope nobady encounters it in the future!
Hi @Anonymous ,
I create a simple sample to test your formulas. They work fine. Can you please share a dummy pbix file? Then we will understand your actual situation clearly and solve it quickly.
Best Regards,
Xue Ding
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |