Forum Discussion
Display Last 3 Reporting Period Column Text
I have a table called ReportPeriod as shown below:
I want to display last 3 month RAG value which is text in RAG Column.
For example, 20/03/2022 is the current month, hence the current RAG is Amber when company A is selected on the Slicer and the Month Slicer.
So I want to get the last 3 month RAG value for Company A which is Amber.
last 3 month RAG value for Company B which is Red.
last 3 month RAG value for Company C which is Amber.
How can I achieve this because I want to display it on a Card visual
Please find the solution in the same drive Here is the file PowerBI Community - Google Drive
Check the Filter for this slicer
Regards,
Ritesh
Please mark the answer if helpful so that it can help others as well
16 Replies
- ribisht17
Super User
Hello,
Step 1.
Max Month = CALCULATE(MAX(( Sheet16[ReportPeriod])), ALL(Sheet16), Sheet16[Company]=MAX(Sheet16[Company]))STEP2.DateDiff = CALCULATE(DATEDIFF(max(Sheet16[ReportPeriod]), [Max Month],MONTH))STEP3.Last 3 Month = IF([DateDiff]=3,MAX(Sheet16[RAG]))Regards,
Ritesh
Please mark the answer if helpful so that it can help others as well
- AnonymousNot applicable
ribisht17 - Thanks for the reply. However, I wanted to avoid creating Calculated Column but to use measure.
I followed the steps you gave by using variable in a measure show below:
Last 3 month = var MaxMonth = CALCULATE(MAX(( Sheet16[ReportPeriod])), ALL(Sheet16), Sheet16[Company]=MAX(Sheet16[Company])) var DateDiff = CALCULATE(DATEDIFF(max(Sheet16[ReportPeriod]), [Max Month],MONTH)) RETURN IF([DateDiff]=3,MAX(Sheet16[RAG]))
I get error stating "A function 'MAX' has been used in a True/False that is used as a table filter expression. This is not allowed"
Please help
- ribisht17
Super User
Here is the Card Pic
NOTE: Filter Last 3 months as NOT BLANK
Regards,
Ritesh
Please mark the answer if helpful so that it can help others as well
- ribisht17
Super User
I have used Measures only
Can you share your data/pbix?
Thanks,
Ritesh
- AnonymousNot applicable
I can't share pbix because I am using organisation account which doesn't allow me due to company policies.
Hence, I have add screenshot of my data model for test data.company ReportPeriod RAG
A 25/12/2021 Amber
A 22/01/2022 Green
A 19/02/2022 Red
A 20/03/2022 Amber
B 25/12/2021 Red
B 22/01/2022 Red
B 19/02/2022 Green
B 20/03/2022 Amber
C 25/12/2021 Amber
C 22/01/2022 Green
C 19/02/2022 Red
C 20/03/2022 AmberCan you share your pbix please.?
When I remove Max, I still get error.
- ribisht17
Super User
What happens if you remove max here
old
var MaxMonth = CALCULATE(MAX(( Sheet16[ReportPeriod])), ALL(Sheet16), Sheet16[Company]=MAX(Sheet16[Company]))new
var MaxMonth = CALCULATE(MAX(( Sheet16[ReportPeriod])), ALL(Sheet16), Sheet16[Company]=(Sheet16[Company])) - ribisht17
Super User
Here is the file PowerBI Community - Google Drive
Check last 3 days folder
Regards,
Ritesh
Please check and let me know
- AnonymousNot applicable
ribisht17 Thanks for your reply.
It works on Table visual and multi-row card but not on card visual.see screenshot below.
There a slicer that Company slicer and reportingdate period slicer and this didn't work on card visual.
- ribisht17
Super User
Please find the solution in the same drive Here is the file PowerBI Community - Google Drive
Check the Filter for this slicer
Regards,
Ritesh
Please mark the answer if helpful so that it can help others as well
- ribisht17
Super User
Hi Ealtim,
Did it help you ? Please mark the answer as solution if helpful so that it can help others as well
Regards,
Ritesh
- Ashish_Mathur
Super User
- AnonymousNot applicable
Ashish_Mathur . Thanks for the reply. But your pbix shows only last month.
What I want to achieve is to get RAG colour for previous 3 month.- Ashish_Mathur
Super User
So what exact result are you expecting in the card visual if 2021 and A are selected in the slicers.
- ribisht17
Super User
Thanks for marking the solution EalTim 🙂
Regards,
Ritesh