topic Re: Calculating number of days data is missing in DAX Commands and Tips

Calculating number of days data is missing

talkprem — Thu, 20 Jun 2024 12:35:38 GMT

Hi All,

I am having a scenario to calculate number of days data is missing from the table in a measure via DAX.

once the data comes it should get reset to 0 else increase by 1 for each day missing.
data started in this example is from 17/6/2024

Sample data -

date	SL	id	S Name	OBBB	Pr Name	Pu No	missing days	this column is not needed
19/6/2024	aaa	10023	site1	er56	material1	1a	2	2 days missing
21/6/2024	aaa	10023	site1	er56	material1	1a	1	1 day missing
22/6/2024	aaa	10023	site1	er56	material1	1a	0	0 days missing

can someone help me to solve this missing days field must check from previous available row of the id to calculate number of days.
there are many id and materials for the same day so it needs to have a combination of id,material
any help is much appreciated

Re: Calculating number of days data is missing

bhanu_gautam — Thu, 20 Jun 2024 13:08:01 GMT

talkprem , You can try to achieve this using Datediff and earlier function

Missing Days =
VAR CurrentDate = 'YourTableName'[Date]
VAR PreviousDate =
CALCULATE(
MAX('YourTableName'[Date]),
FILTER(
ALL('YourTableName'),
'YourTableName'[Date] < CurrentDate &&
'YourTableName'[NameOBBBPr] = EARLIER('YourTableName'[NameOBBBPr]) &&
'YourTableName'[NamePu] = EARLIER('YourTableName'[NamePu])
)
)
RETURN
IF(ISBLANK(PreviousDate), 0, DATEDIFF(PreviousDate, CurrentDate, DAY) - 1)

Re: Calculating number of days data is missing

talkprem — Thu, 20 Jun 2024 14:40:50 GMT

The measure is not working can you help me bro?

getting error

Re: Calculating number of days data is missing

bhanu_gautam — Tue, 25 Jun 2024 13:34:34 GMT

talkprem , I have made changes in DAX attaching PBIX file with required output