Calculation the quantity for non-existent data in the table

[Karlie69]

Hi 🙂

The problem I have is as follows:
I have a table that stores the audit results. The audit is carried out once or twice a month. Sometimes the audit should be carried out twice, but it was not even once (therefore no new records appeared in the table).

I would like to create a table, anything that would show me that in a given month, an audit was not carried out even once. But how to do it, if no new records have been registered in the table?

Below is my solution that works only when at least one audit has been carried out. I just added a conditional column to the register, which returned to me the number 2 (so two audits should be performed) - this is "Count of audits to be performed"
The "Count of performed audits" column is based on the date column (I set it not to show me the date, only the number of dates)
Left to audit - I just subtracted the second column from the first.

Area	Count of performed audits	Count of audits to be performed	Left to audit
Administration	1	2	1

I hope that my problem is comprehensible. Thank you in advance!

[v-kelly-msft · Accepted solution]

Hi @Karlie69 ,

You should also have an audit table containing all the audit data,right?

Suppose that you have a table like below:

Area	Expected performed times
Administration	3
a	2
b	1
c	2
d	1

Then create a calculated column as below：

Count of performed audits = LOOKUPVALUE('Table'[Count of performed audits],'Table'[Area],'Table (2)'[Area],0)

And you will see:

For my sample .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Karlie69 , Join if with date table, if you do not have date create one using month year.

Now create a measure like this and plot it with month year from date table 

if(isblank(count(audit[audit])),1, blank())