Forum Discussion
Calculation the quantity for non-existent data in the table
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!
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,
KellyDid I answer your question? Mark my post as a solution!
2 Replies
- amitchandak
Super User
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())
v-kelly-msftCommunity Support
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,
KellyDid I answer your question? Mark my post as a solution!
