Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jazzhanya
Frequent Visitor

Display KPI Light Last Month Status in Report Table

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: Smiley Happy

Amber as Overall RAG: Smiley Indifferent

Red as Overall RAG: Smiley Sad

 

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!!

 

Capture.PNG

5 REPLIES 5
jazzhanya
Frequent Visitor

 

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!

 

pbi.PNG

 

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-jiascu-msft

 

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. 

 

Only 4 suppliers shown (Hanau is missing)Only 4 suppliers shown (Hanau is missing)

 

Previous screenshotPrevious screenshot 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

 

 

jazzhanya
Frequent Visitor

Any answer / good tips please..?

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.