Forum Discussion
RAG Status Based Based On Invoice Date
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
Hi Anonymous ,
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] + 30Create 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 TeamIf 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!
2 Replies
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-Forum/ba-p/963216
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/1447523Hi Anonymous ,
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] + 30Create 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 TeamIf 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!
