topic Re: List of items not available on Previous Day in DAX Commands and Tips

List of items not available on Previous Day

Aku_2800 — Mon, 13 May 2024 09:33:58 GMT

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

Re: List of items not available on Previous Day

PurpleGate — Mon, 13 May 2024 10:30:24 GMT

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())

Re: List of items not available on Previous Day

Aku_2800 — Mon, 13 May 2024 11:36:34 GMT

this won't work..

Re: List of items not available on Previous Day

Aku_2800 — Mon, 13 May 2024 11:41:18 GMT

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

Re: List of items not available on Previous Day

xifeng_L — Mon, 13 May 2024 14:23:10 GMT

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~