DAX Measure - calculate difference between 2 date values and display certain value

Question

Hi Power BI community,&nbsp;I hope you will be able to help me with an issue I have. Be advised that I am a complete beginner with Power BI with only 2 weeks of experience using it, particularly with building visuals rather than modeling or creating DAX measures.&nbsp;I am working on a project that requires me to create a DAX measure to calculate the difference between some dates depending on certain conditions and display a relevant message on the visual. For instance, IF the most recent milestone was delivered on time or early, and a publishing CheckDate is today or in the future, and there is no Publishing EndDate, THEN display "On Track".&nbsp;I have attached an image of the visual to provide some context of the table visual in which this measure is to be used.&nbsp;A full list of requirements to check is available below:Overall Course Delivery StatusCondition v2&nbsp;&nbsp;&nbsp;&nbsp;On trackMost recent milestone was delivered on time or early and publishing checkdate is today or in the future and there is no publishing end date&nbsp;&nbsp;&nbsp;&nbsp;At risk (low)Most recent milestone checkdate is not the publishing checktype + has no end date + today is 1-2 days after that check dateORMost recent checkdate is not the publishing checktype was delivered 1-2 days lateRunning 1-2 days lateMost recent checkdate is the publishing checktype + has no publishing end date + today is 1-2 days after the publishing check date&nbsp;&nbsp;&nbsp;&nbsp;At risk (medium)Most recent checkdate is not the publishing checktype + has no end date + today is 3-5 days after that check dateORMost recent checkdate is not the publishing checktype was delivered 3-5 days lateRunning 3-5 days lateMost recent checkdate is the publishing checktype + has no publishing end date + today is 3-5 days after the publishing check date&nbsp;&nbsp;&nbsp;&nbsp;At risk (high)Most recent checkdate is not the publishing checktype + has no end date + today is 5-10 days after that check dateORMost recent checkdate is not the publishing checktype was delivered 5-10 days lateRunning 5-10 days lateMost recent checkdate is the publishing checktype + has no publishing end date + today is 5-10 days after the publishing check date&nbsp;&nbsp;&nbsp;&nbsp;At risk (very high)Most recent checkdate is not the publishing checktype + has no end date and today is 11+ days after that check dateORMost recent checkdate is not the publishing checktype was delivered 11+ days lateRunning 11+ days lateMost recent checkdate is the publishing checktype + has no publishing end date + today is 11+ days after the publishing check date&nbsp;&nbsp;&nbsp;&nbsp;Published on timePublishing checkdate = publishing end date&nbsp;&nbsp;&nbsp;&nbsp;Published 1-2 days latePublished end date is 1-2 days after check date&nbsp;&nbsp;&nbsp;&nbsp;Published 3-5 days latePublished end date is 3-5 days after check date&nbsp;&nbsp;&nbsp;&nbsp;Published 5-10 days latePublished end date is 5-10 days after check date&nbsp;&nbsp;&nbsp;&nbsp;Published 11+ days latePublished end date is 11+ days after check date&nbsp;&nbsp;&nbsp;&nbsp;Published 1-2 days earlyPublished end date 1-2 days before publishing check date&nbsp;&nbsp;&nbsp;&nbsp;Published 3-5 days earlyPublished end date is 3-5 days before publishing check date&nbsp;&nbsp;&nbsp;&nbsp;Published 5-10 days earlyPublished end date is 5-10 days before publishing check date&nbsp;&nbsp;&nbsp;&nbsp;Published 11+ days earlyPublished end date is 11+ days before checkdate&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Please let me know if you need any additional information that could help with this request.&nbsp;Best regards,Mrisho.

anonymous · Answer

You can calculate the days between two dates using the DATESBETWEEN() function in a measure or a new column.

To create an if statement - Create a conditional column under transform data - add column tab - custom column - create the code - EX. if dates between <= expected date & CheckDate >= Date.From(DateTime. LocalNow()) & PublishingEndDate = NULL THEN "On Track" ELSE " "

You can then add that custom column to your table or wherever you may please.

Let me know if you need more clarifying

anonymous · Answer

You can incorporate the days between two dates using this code in the custom column instead if you want as well :&nbsp;each Duration.Days([Date1] - [Date2])

anonymous · Answer

Hi&nbsp;Anonymous&nbsp;,
Have you solved your problem? If solved please mark the reply in this post which you think is helpful as a solution to help more others facing the same problem to find a solution quickly, thank you very much!
Best Regards,
Xianda Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

anonymous · Answer

Thanks for sharing,&nbsp;Anonymous. Sorry for the delay in getting back to you.&nbsp;Unfortunately, that suggestion did not work for me. But thankfully, I was able to find a solution with help from elsewhere.

Forum Discussion

DAX Measure - calculate difference between 2 date values and display certain value

4 Replies

Related content

DAX Calculated Measure

Calculation Groups - Difference measures

Dynamic Calculation Different Period

What is the difference between a measure and calculated column

Different Calculations

Overall Course Delivery Status	Condition v2
On track	Most recent milestone was delivered on time or early and publishing checkdate is today or in the future and there is no publishing end date
At risk (low)	Most recent milestone checkdate is not the publishing checktype + has no end date + today is 1-2 days after that check date	OR	Most recent checkdate is not the publishing checktype was delivered 1-2 days late
Running 1-2 days late	Most recent checkdate is the publishing checktype + has no publishing end date + today is 1-2 days after the publishing check date
At risk (medium)	Most recent checkdate is not the publishing checktype + has no end date + today is 3-5 days after that check date	OR	Most recent checkdate is not the publishing checktype was delivered 3-5 days late
Running 3-5 days late	Most recent checkdate is the publishing checktype + has no publishing end date + today is 3-5 days after the publishing check date
At risk (high)	Most recent checkdate is not the publishing checktype + has no end date + today is 5-10 days after that check date	OR	Most recent checkdate is not the publishing checktype was delivered 5-10 days late
Running 5-10 days late	Most recent checkdate is the publishing checktype + has no publishing end date + today is 5-10 days after the publishing check date
At risk (very high)	Most recent checkdate is not the publishing checktype + has no end date and today is 11+ days after that check date	OR	Most recent checkdate is not the publishing checktype was delivered 11+ days late
Running 11+ days late	Most recent checkdate is the publishing checktype + has no publishing end date + today is 11+ days after the publishing check date
Published on time	Publishing checkdate = publishing end date
Published 1-2 days late	Published end date is 1-2 days after check date
Published 3-5 days late	Published end date is 3-5 days after check date
Published 5-10 days late	Published end date is 5-10 days after check date
Published 11+ days late	Published end date is 11+ days after check date
Published 1-2 days early	Published end date 1-2 days before publishing check date
Published 3-5 days early	Published end date is 3-5 days before publishing check date
Published 5-10 days early	Published end date is 5-10 days before publishing check date
Published 11+ days early	Published end date is 11+ days before checkdate