Latest Submission Date VS Current Month

[NickzNickz]

Hi ...

In my table, I have a column name Submission Date (Ex. Mac 2023).

First I believe we need to create a measure for Month = Current Month - 1 Month.
Secondly, I need to create a measure for the Submission Date Indicator =

If Submission Date >= Month then the background colour is green.
If Submission Date < Month, the background colour is red.

Hope someone can help me with this. Your help is much appreciated.

Regards,

NickzNickz

[SamInogic]

Hi @NickzNickz ,

As per our understanding your requirement is to conditionally format the column’s background if the Submission date is greater than current month/Year to Green otherwise if the Submission date is older than the current month/year then colour should be red.

You can achieve this by following below steps:

1. Create a column in the table with below DAX expression:

Background Color = IF(DATE(YEAR(Submission[Submission Date].[Date]) , MONTH(Submission[Submission Date].[Date]), 1)>= DATE(YEAR(TODAY()), MONTH(TODAY()), 1), "Green",if(DATE(YEAR(Submission[Submission Date].[Date]) , MONTH(Submission[Submission Date].[Date]), 1) < DATE(YEAR(TODAY()), MONTH(TODAY()), 1),"Red",""))

 
2. You can optionally create a column to show only Month and Year of the Submission Date with below DAX expression:

Submission Month And Year = FORMAT(Submission[Submission Date].[Date], "mmm") & " " & YEAR(Submission[Submission Date].[Date])

3. Add these columns to the table as shown in the below screenshot.

 

4. Now to add conditional formatting to the column you can click on respective column i.e. Submission Date and select conditional Formatting option and click on Background color. Please refer to the below screenshot for the same.

 

 5. This will open a dialog box where you need to select the field Background Color that we created and click on OK.

 

6. This will result as follows as per the requirement.

 

If this answer helps, please mark it as an Accepted Solution so it would help others to find the solution.

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

[NickzNickz]

Hi @SamInogic ,

First of all I am very grateful for your efforts and time...

I have tested in my development enviroment and I found out that, April 2023 still red (as result below) ... April 2023 suppost to be green also because current month we need to minus 1 month ...

Formula : Month = Current Month - 1 Month 
Answer : Month = April 2023

How can we achieve that  .....

Regards,

NickzNickz

[SamInogic · Accepted solution]

Hi @NickzNickz ,

Can you try below updated DAX for Background color field as listed in above steps:

Background Color = IF(DATE(YEAR(Submission[Submission Date].[Date]) , MONTH(Submission[Submission Date].[Date]), 1)>= DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1), "Green",if(DATE(YEAR(Submission[Submission Date].[Date]) , MONTH(Submission[Submission Date].[Date]), 1) < DATE(YEAR(TODAY()), MONTH(TODAY())-1, 1),"Red",""))

This will result as follows:

If this answer helps, please mark it as an Accepted Solution so it would help others to find the solution.

Thanks!

Inogic Professional Service Division

An expert technical extension for your techno-functional business needs

Power Platform/Dynamics 365 CRM

Drop an email at crm@inogic.com

Service:  http://www.inogic.com/services/ 

Power Platform/Dynamics 365 CRM Tips and Tricks:  http://www.inogic.com/blog/

[NickzNickz]

Hi ... 
Dear @SamInogic ,

Thank you for your prompt  reply.

I have tested and its was great .... 

Thank you so much ...

Regards,

NickzNickz