Checking if an inspection sheet has been submitted, if not, is it overdue?

[bonjourposte]

Hi all, we've had problems with inspectors not returning completed inspection sheets, so I want a table of inspectors, and the number of days by which an inspection sheet is late, if it hasn't been submitted yet.  (I'm giving them 14 days to submit it). 

This is what I want it to look like:

Furthermore, if it's overdue, I want a gradient of soft red to deep red the later it is.  

Here is the relevant part of my Inspection Sheet table.  The "Overdue?" column is today's date minus the date I gave them their inspection sheet.  The line with the red circle denotes an inspection sheet that wasn't submitted.

Here is my measure to see if the inspection meets both conditions of 1) inspection sheet not submitted (ie. if the Submitted column is blank), and 2) if it's been longer than 14 days since they got the inspection sheet.  

Overdue CondFormtg =

IF(

    AND(

        ISBLANK(MIN('Inspection Count'[Date Inspection Submitted])),

        MIN('Inspection Count'[Overdue?])>14

        ),

        MIN('Inspection Count'[Overdue?]

        ))

This gives me a blank table:

Initially I had mis-written the code to check to see if the column WASN'T blank, like this:

Overdue CondFormtg =

IF(

    AND(

        NOT(ISBLANK(MIN('Inspection Count'[Date Inspection Submitted]))),

        MIN('Inspection Count'[Overdue?])>14

        ),

        MIN('Inspection Count'[Overdue?]

        ))

And I got this: 

How do I get the measure to just give me the number of days over 14 if the "Date Inspection Submitted" column is blank?

Thanks!

[Anonymous · Accepted solution]

Hi @bonjourposte , @Wilson_ @TomMartens Thank you for your prompt reply!
Based on your description, I create some sample data for your reference, If I misunderstand your issue, please feel free to reply.

• Create a Measure as shown below:

FilterInspector = IF(AND(ISBLANK(SELECTEDVALUE('Table'[Date Inspection Submitted])), 'Table'[OverDue]>14), 1, 0)

• Then add a new table visual to filter it use the measure:
  
• For conditional formatting of the Overdue, select the field and choose the format you want:

Result:

Best regards,

Joyce

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

[Wilson_]

Hi bonjourposte,

I assume one inspector can have multiple inspection records? (I cannot tell from the data you chose to share whether that is the case or not.)

If so, calculating MIN of date inspection submitted and MIN of Overdue? is likely not actually doing what you want it to do. You can test this by creating a measure that is just that piece of your measure (ie: MIN('Inspection Count'[Date Inspection Submitted]) and MIN('Inspection Count'[Overdue?]). Add those two to your table as well and see what it returns.

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.

[TomMartens]

Hey @bonjourposte ,

create a pbix that contains sample data but still reflects your semantic model (tables, relationships, calculated columns, and measures), upload the pbix to OneDrive, Google Drive, or Dropbox and share the link. If you are using a spreadsheet as a data source for the sample data instead of using the manual import method share the spreadsheet as well.

Do not forget to explaing the expected result based on the sample data you provide.

Regards,

Tom