topic Adding a new date column to bring colored column that pasted 6 weeks breach in DAX Commands and Tips

Adding a new date column to bring colored column that pasted 6 weeks breach

AstaBr — Wed, 21 Aug 2024 10:40:49 GMT

Hi,

What would be the way to get the above implemented on the live data please. We need to get one of the worksheets on the PowerBI to color if it pasted 6 weeks review date (if the review was not left on the project for more then 6 weeks) and another with oppsite, the date column which is due (within 4 weeks ahead) to get colored.

Re: Adding a new date column to bring colored column that pasted 6 weeks breach

Rupak_bi — Wed, 21 Aug 2024 12:51:42 GMT

Unable to understand. Please share sample data and then explain the requirement

Re: Adding a new date column to bring colored column that pasted 6 weeks breach

AstaBr — Wed, 21 Aug 2024 22:17:43 GMT

Hi,

There is a column with 'Latest updated date' which i want to change colour if staff haven't updated the project over the 6 weeks. Currently, I do it manualy in conditional formating which is not what i would like to keep on doing. If there is some kind of formula that we can create please?

Re: Adding a new date column to bring colored column that pasted 6 weeks breach

Anonymous — Thu, 22 Aug 2024 07:12:17 GMT

Thanks for the reply from Rupak_bi , please allow me to provide another insight:

Hi, AstaBr

Regarding the issue you raised, my solution is as follows:

1.First I have created the following table and the column names and data are the data you have given:

2. Below are the measure I've created for your needs:

MEASURE = IF ( DATEDIFF ( MAX ( 'Table'[The date of the latest update] ), TODAY (), WEEK ) >= 6, "RED" )

3.Then, as shown in the image, modify the settings of the visualization:

4.Here's my final result, which I hope meets your requirements.

5.Here is the relevant documentation:

DATEDIF function - Microsoft Support

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Adding a new date column to bring colored column that pasted 6 weeks breach

Rupak_bi — Thu, 22 Aug 2024 07:15:52 GMT

You can do it by creating a flag based on your logic in calculated column and then refer that flag column in conditional formatting and defining different colour of flag values. Flag values will be numbers

Re: Adding a new date column to bring colored column that pasted 6 weeks breach

AstaBr — Tue, 27 Aug 2024 09:47:07 GMT

That's amaizingm thank you ever so much.

Re: Adding a new date column to bring colored column that pasted 6 weeks breach

AstaBr — Mon, 02 Sep 2024 08:26:48 GMT

Hi,

Could you please help me to create a measure for the above as well?

Re: Adding a new date column to bring colored column that pasted 6 weeks breach

AstaBr — Mon, 02 Sep 2024 14:35:51 GMT

Could you also help me out doing similar thing on the data that has past date and will be expiring within 2 weeks please? I have a column for 'QIA/EIA expected Completion Date' and we need to highlight the columns that are expired or will within next 2 weeks.