Forum Discussion

jazzhanya's avatar
jazzhanya
Frequent Visitor
8 years ago

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 foll...
jazzhanya's avatar
jazzhanya
Frequent Visitor
8 years ago

 

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

 

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

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

  • jazzhanya's avatar
    jazzhanya
    Frequent Visitor
    8 years ago

    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)

     

    Previous 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