Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone,
In my data, I have one column called "Year/Month". I want the last table column (marked in red) shows the last "Year-Month" status of Overall RAG color (makred in blue) with the following logics:
Green as Overall RAG:
Amber as Overall RAG:
Red as Overall RAG:
However, the colume doesn't return me the correct faces for the last status, as shown in the picture. I also tried to use conditional color formating instead of smile face, but it didn't work either. Can anyone help on my case? Thank you in advance!!
Hello
Sorry for my late reply. I changed the KPI light pattern format into traffic light.. The traffic light color is correct.
My problem here is I want to present only the last "Month-Year" KPI status of each supplier site. (please see below screenshot)
In the data, not all the supplier sites has the up-to-date data ( in this case last month is 2018-06). Some might have the last record in last month-1 "2018-05", last month-2 "2018-04" or evern earlier ( an exemple below is the supplier site Hanau).
Here is the formula I write:
LastMonth = IF(YEAR(TODAY())= YEAR('SM KPIs'[Month-Year]) && MONTH(TODAY())-1 = MONTH('SM KPIs'[Month-Year]);1;IF(YEAR(TODAY())= YEAR('SM KPIs'[Month-Year]) && MONTH(TODAY())-2 = MONTH('SM KPIs'[Month-Year]);2;IF(YEAR(TODAY())= YEAR('SM KPIs'[Month-Year]) && MONTH(TODAY())-3 = MONTH('SM KPIs'[Month-Year]);3;BLANK())))
But the problem of this formula is .. it will present last month, last month-1, and last month-2 data at the same time. And I only want the report table to return only one line of the last record status for each supplier site.
Can you please advise how I can solve this problem? Thank you!
Jaz
Hi @jazzhanya,
How about keeping other lines blank? Please try a formula like below. Can you share a sample?
LastMonth = IF ( YEAR ( TODAY () ) = YEAR ( 'SM KPIs'[Month-Year] ) && MONTH ( TODAY () ) - 1 = MONTH ( 'SM KPIs'[Month-Year] ); 1; BLANK () )
Best Regards,
Dale
Thank you for your support!
If I reduce the formula to show Year & Month - 1, I will lose the visibility of those supplier sites whose last update are before June. Please see my screeshot below, the supplier site "Hanau" as an example.
I'm think to add another condition to my last formula:
LastMonth =
IF(
YEAR(TODAY ( ) ) = YEAR ( 'SM KPIs' [Month-Year]) && MONTH ( TODAY ( ) ) -1
= MONTH ( 'SM KPIs' [Month-Year] );
1;
IF (
YEAR( TODAY ( ) ) = YEAR ( 'SM KPIs' [Month-Year] ) && MONTH ( TODAY ( ) ) -2
= MONTH ( 'SM KPIs' [Month-Year] );
1;
IF (
YEAR(TODAY ( ) ) = YEAR ( 'SM KPIs' [Month-Year] ) && MONTH( TODAY ( ) ) -3
= MONTH( 'SM KPIs' [Month-Year] );
1;
BLANK ( ) ) ) )
My thought is to add an additonal formula..
for each supplier, sumlastmonth = sum ( 'last month' )
If sumlastmonth = 3, return Year & Month -1,
If sumlastmonth = 2, return Year & Month -2,
If sumlastmonth = 1, return Year & Month -3, else blank
The problem of this formula is that if the last update of the supplier site is before April, no KPI light will be displayed.
Can you please advise on it? Thank you!!
Kind regards,
Jaz
Hi @jazzhanya,
As we can see from the first image, the last six columns are the same. So what's the logic here? Can you share the formula you used? For example, [Sales] >= 100, Red (face 1); [Sales] < 100, Green (face 2).
Share some data if you can.
Best Regards,
Dale
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |