show dates with no data

[Anonymous]

hello i have a table called carriers and a date table with all days of calender.

i want to find which carrier has no data o na specific date to show in a table in dashboard.

here;s an example:

imagine that today is 7 january so todays date, i usually get the data 1 day before 7 jan so last dates i receive data should be 6 jan for carrier A,B and C, but as you see below, i only have B that shows me 6 jan, and i have missing days too that arent all fileld from 1 jan to 6 jan.

heres the ful lexample:

i have the given data on me with values on these dates:

carrier	date
A	1-Jan
A	3-Jan
A	5-Jan
B	6-Jan
C	3-Jan

and a calender table i created,

i want the results on the dashboard to be like this where it shows me when i dont have data :

carrier	missing date
A	2-Jan
A	4-Jan
A	6-Jan
B	1-Jan
B	2-Jan
B	3-Jan
B	4-Jan
B	5-Jan
C	1-Jan
C	2-Jan
C	4-Jan
C	5-Jan
C	6-Jan

any help please?

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You can try the following methods.

Date Table:

Date = CALENDAR(MIN('Table'[date]),MAX('Table'[date]))

     

Measure:

Missing = IF(SELECTEDVALUE('Date'[Date])<>SELECTEDVALUE('Table'[date]),1,0)

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jamilqpr]

Yes, You can see results on the dashboard by Calculating Measure Countrows to data to calculate Carrier, It will show you, carrier count. then Select Bar chart in legend select missing date and carrier axix label. Graph and pie chart and table you can make easily