Forum Discussion
Find Missing Dates in a Table
Hi,
I have the challenge of detecting if there are missing dates according to the records of a table, those missing dates, would belong to a corresponding Fiscal Week, I need to know how many fiscal weeks are missing in the table.
Table 1 - Actual Date Column. Multiple records from multiple individuals. In this Column is where I would Like to detect if there is a Week Missing. | Calendar Table - Fiscal Week Value, According to a Unique Date
|
For making myself clear, I would like to visualize if theres a week missing on those dates. For example if there's no record at all for the 2nd fiscal week on the table.
Currently I'm just able to count the number of weeks that have been uploaded in total, but I have no clue yet if there is any missing weeks for an individual for example.
Measure for counting the number of weeks uploaded:
Missing Weeks in Data Set = CALCULATE(DISTINCTCOUNT(Calendar[WeeKFY2]), DATESBETWEEN(Calendar[Date],FIRSTDATE('Table1'[Actual Date]), LASTDATE('Table1'[Actual Date])))
How could I achieve a measure to know if there are missing weeks on the data? I would Like for the measure to give me 0 if there are no missing weeks for example.
I don't know if there might be a specific function for this.
Thanks!
7 Replies
- fhill
Resident Rockstar
Assuming there's a link between your Date Table, and the List of Dates, just build this Visual... no Code required.
** Table with 'WeekNum' (your WeekFY), *Don't Summarize' and *Show Items with no Values* then just COUNT your Actual Date Column next to it. **
Plus you can Filter the visual now looking for 'Count of Actual Date' is Blank, to only get the WeekNums with NO matching Dates.
Hey fhill !
This works, but it ain't pretty useful fo my need right now.
If I do it that way, I would found no missings because those records are uploaded by same users, so I need to found only those specific cases of those users who didn't uploaded a record in the whole year. So with your approach I would have to go user per user, reviewing every week to see if I found a missing record. So thtat's why I need code... is a measure that tells me in a punctual way how many records are missing, and from whom.
Thanks for the reply
- v-diye-msft
Community Support
HI Anonymous
I believe you can try the Except function to see the detailed missing records, and the count the rows with number.
https://docs.microsoft.com/en-us/dax/except-function-dax
https://docs.microsoft.com/en-us/dax/countrows-function-dax
https://docs.microsoft.com/en-us/dax/distinctcount-function-dax
- amitchandak
Super User
Anonymous , create a date table and join with it with a week data. Now create a measure with +0 and filter = 0, you will get the missing weeks
if([measure]+0 =0, 1,blank()) // measure in table joined with date table
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184Another way use except for -https://docs.microsoft.com/en-us/dax/except-function-dax
Appreciate your Kudos.I have a Date Table named Calendar, which is where the Fiscal Week has been calculated on another column. And the Calendar table is joined with the actual date from my Table1.
I didn't understood what should have the new measure that I create.
"if([measure]+0 =0, 1,blank())"
Thanks for the reply,
- v-diye-msft
Hi Anonymous
If the above posts help, please kindly mark it as a answer to help others find it more quickly. thanks!
If not, please kindly elaborate more.
I have a dataset, where it has employeename, timesheet date, timesheet status for the days employee worked. I want to display in Matrix visualization for all days irrespective of his action (Approved, submitted, never submitted)
Please let me know, what fucntion would help
