RAG Status Based Based On Invoice Date

[tksnota]

Hi,

I need help to show an item in RAG based on due date using terms like 30, 45 or 60 days after invoice date.

Example: Red = 1 day after invoice, Amber = 5 days before Invoice date and Green more than 5 days.

Thanks

[v-huijiey-msft · Accepted solution]

Hi @tksnota ,

Thanks for the reply from @lbendlin .

Here is the sample data I created:

User ID	Invoice Date
A1	5/29/2024
A2	5/31/2024
A3	5/1/2024
A4	4/28/2024
A5	5/17/2024
A6	2/28/2024
A7	5/9/2024

Assuming 30 days due, create a calculated column to represent the due date corresponding to the invoice date.

Due Date = 'Table'[Invoice Date] + 30

Create a calculated column to determine the RAG status.

RAG = 
VAR CurrentDate = TODAY()
VAR DaysToDueDate = DATEDIFF(CurrentDate,'Table'[Due Date], DAY)
RETURN
SWITCH(
    TRUE(),
    DaysToDueDate = 1, "Red",
    DaysToDueDate <= -5 , "Amber",
    DaysToDueDate >= 5, "Green"
)

Set conditional formatting:

As you can see, the Amber color cannot be recognized and can be replaced with other colors.

The final page effect is as follows:

I would be grateful if you could provide me with the pbix file or sample data.

Remember to remove sensitive data and do not log in to your account in Power BI Desktop when uploading the pbix file.

If you have any other questions please feel free to contact me.

The pbix file is attached.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...