Find Missing Dates in a Table

[Anonymous]

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!

[aknewbie]

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

[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.

[amitchandak]

@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#M184

Another way use except for -https://docs.microsoft.com/en-us/dax/except-function-dax


Appreciate your Kudos.

[Anonymous]

@amitchandak 

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,

[fhill]

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.

[Anonymous]

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]

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