Create a table of values over last several days

[mll92]

Hello,

I need to create a five-day log showing overall system status each day. Changes to a status may be made multiple times a day, or may not be made at all. As an example, I have the following table:

Date                                 System      Rating   Rating#

12/15/2022 11:50 a.m.	Microsoft	Good	1
12/15/2022 2:00 p.m.	Microsoft	Okay	2
12/16/2022 8:00 a.m.	Facebook	Bad	3
12/17/2022 1:00 p.m.	Microsoft	Good	1
12/18/2022 1:00 p.m.	Facebook	Good	1

What I need to do is return either the "worst" (max) rating per day (3 being the max) or, if there is no value entered for the day, then I need the last value entered for that category. 

Therefore, my tracker should look like this: 

	12/19/2022	12/18/2022	12/17/2022	12/16/2022	12/15/2022
Microsoft	Good	Good	Okay	Okay
Facebook	Good	Bad	Bad	Bad	Okay

I haven't been able to figure this out. Any suggestions?

[johnt75]

If you have a date table and the date & time stored as separate columns in your fact table you can use

Daily Rating =
VAR CurrentDate =
    MAX ( 'Date'[Date] )
VAR CurrentRating =
    SELECTCOLUMNS ( TOPN ( 1, 'Table', 'Table'[Rating#] ), "@val", 'Table'[Rating] )
VAR PrevRating =
    SELECTCOLUMNS (
        CALCULATETABLE (
            TOPN ( 1, 'Table', 'Table'[Date], DESC, 'Table'[Time], DESC ),
            'Date'[Date] < CurrentDate
        ),
        "@val", 'Table'[Rating]
    )
RETURN
    COALESCE ( CurrentRating, PrevRating )

[mll92]

Thanks! How would I go about changing this to show the "worst" status if the status changes after midnight? For example, if Microsoft is "Bad" until 12:30 a.m. then changes to "Okay," the dashboard needs to show "bad" for the day because that is the worst status for the day.