Calculating number of days data is missing

[talkprem]

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

[bhanu_gautam]

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

[bhanu_gautam]

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

[talkprem]

The measure is not working can you help me bro?

getting error