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

[iammrishoabeid]

Hi Power BI community,

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.

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".

I have attached an image of the visual to provide some context of the table visual in which this measure is to be used.

A full list of requirements to check is available below:

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

Please let me know if you need any additional information that could help with this request.

Best regards,

Mrisho.

[Anonymous]

Hi @iammrishoabeid ,

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]

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

[Anonymous]

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

[iammrishoabeid]

Thanks for sharing, @Anonymous. Sorry for the delay in getting back to you.

Unfortunately, that suggestion did not work for me. But thankfully, I was able to find a solution with help from elsewhere.