Forum Discussion

pradeepyadav921's avatar
pradeepyadav921
Frequent Visitor
2 years ago
Solved

Calculating sla status for the previous date

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

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.