Calculating sla status for the previous date

[pradeepyadav921]

Hi team, 

I am trying to create a table where there is four header as category, today date, yesterday date and day before yesterday date. I have a sheet where headers are category, file name, date and sla status. Now I want to create a traffic light system table where suppose if we have category A and under category A there is 10 files if all files have sla status as met it should show green for whatever date I am selecting if on 9 files have sla status as met it should red as traffic light system. 

I have tried but I am able to calculate value for the date I am selecting from a date picker but for yesterday and day before yesterday it is giving a blank value. Can anyone help me with the DAX

[v-yilong-msft · Accepted solution]

Hi @pradeepyadav921 ,

I create a table as you mentioned.

Then I create two measures, here are the DAX codes.

SLAMetCount =
VAR _A = CALCULATE ( COUNTROWS ( 'Table' ) ) RETURN IF ( _A = 1, 1, 0 )

Measure = 
VAR _SLAMetToday = 'Table'[SLAMetCount]
RETURN
    IF (
        _SLAMetToday = 10,
        UNICHAR ( 128994 ),
        IF ( _SLAMetToday >= 9, UNICHAR ( 1288993 ), UNICHAR ( 128308 ) )
    )

I think you can use this DAX code for a try.

SLAMetCountYesterday = CALCULATE(
    [SLAMetCount],
    DATEADD('YourTable'[Date], -1, DAY)
)

Best Regards

Yilong Zhou

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