List of items not available on Previous Day

[Aku_2800]

I have a dataset which shows volumes of devices per day.
NOTE - Every device may not have volume per day.
So, there is a list of unique devices (6). Everyday, the devices run and generate volumes. It could be 0(Zero) too but such devices are not mentioned. Hence, I want a list in matrix/table visual letting client know which devices didn't run(no volumes generated) previous day.

Example :- 
In the below example - I can select whatever date in a slicer and then it should return the devices which generated 0 volume previous day.
In this case if I select 13th May, it will check on 12th.
Unique Devices - A1,B1,C1,D1,E1 and F1
The devices which didn't generate volume previous day (12th May) were - A1,C1,D1,E1 and F1

Date	Device	Volume
13-05-2024	B1	32
13-05-2024	A1	21
13-05-2024	C1	22
12-05-2024	B1	11

[xifeng_L · Accepted solution]

Hi @Aku_2800 ,

You can try below measure.

no volumes generated devices = 
VAR SelectedDate = SELECTEDVALUE('Calendar'[Date])
RETURN
IF(SelectedDate<>BLANK(),
    CONCATENATEX(
        FILTER(
            ALL('Unique Devices'[Unique Devices]),
            CALCULATE(SUM('Fact'[Volume]),'Calendar'[Date]=SelectedDate-1)=BLANK()
        ),
        'Unique Devices'[Unique Devices],
        " ; "
    ),
    "Pls select the date first!"
)

Demo - List of items not available on Previous Day.pbix

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[Aku_2800]

this won't work..

[PurpleGate]

 This will show you the previous days value for each device.

Measure name= CALCULATE(SUM('Table'[ColumnVolume]),DATEADD('Date'[Date],-1,DAY))

 Then you can filter on at on that measure, so if it is blank, it returns the device ID

Measurefilter = IF(isblank([Measure name]),SELECTEDVALUE('Table'[Device]),BLANK())

[Aku_2800]

0 Volume =

VAR _MaxDate =

    MAX ( Table[Date] )

VAR _DevicesAvailable =

    CALCULATETABLE(VALUES ( Table[DeviceName] ),Table[DeviceName]<>BLANK())

VAR _DevicesAvailableOnCurrentDay =

    CALCULATETABLE ( VALUES(Table[DeviceName]),Table, Table[Date] = _MaxDate,Table[DeviceName]<>BLANK())

VAR _DevicesNotAvailable =

    EXCEPT (

        _DevicesAvailable,

        SELECTCOLUMNS ( _DevicesAvailableOnCurrentDay, Table[DeviceName] )

    )

RETURN

    _DevicesNotAvailable


This will just give the devices which are not available on max date (out of all distinct devices). If I do max date - 1. It will check for 1 day before max date but still this is not dynamic. I want this to be dynamic where as I select a date from a slicer in Canvas and it should return the list of values only