Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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
@talkprem , I have made changes in DAX attaching PBIX file with required output
Proud to be a 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)
Proud to be a Super User! |
|
The measure is not working can you help me bro?
getting error
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
10 | |
9 | |
8 | |
7 | |
6 |
User | Count |
---|---|
14 | |
13 | |
11 | |
9 | |
8 |