Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Anonymous
Not applicable

Date comparison

Hi,

 

This is my table:

IDStatusDate1Date2
112020-01-21 22:45:322020-02-01 12:00:00
212020-01-15 16:25:582020-02-03 15:45:00
302020-02-02 02:13:222020-02-01 04:00:00
412020-02-03 04:52:122020-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?

6 REPLIES 6
JarroVGIT
Resident Rockstar
Resident Rockstar

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! 🙂





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




amitchandak
Super User
Super User

The formula seems fine. Just check if the data types of the dates time column has been detected as DateTime or not.

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

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.

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.
Anonymous
Not applicable

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.

1.PNG

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.

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.