Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
talkprem
Helper I
Helper I

Calculating number of days data is missing

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 - 

dateSLidS NameOBBBPr NamePu Nomissing days this column is not needed
19/6/2024 aaa 10023 site1 er56 material1 1a 2 days missing
21/6/2024 aaa 10023 site1er56 material1 1a 1 day missing
22/6/2024 aaa 10023 site1er56 material1  1a 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

3 REPLIES 3
bhanu_gautam
Super User
Super User

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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






bhanu_gautam
Super User
Super User

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




Did I answer your question? Mark my post as a solution! And Kudos are appreciated

Proud to be a Super User!




LinkedIn






The measure is not working can you help me bro?

getting error

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.