inspection checklist cross reference

[AspiringAnalyst]

My business case is that I have some Health and Safety inspections that need done at our site.  I have a master excel list of items that need inspected monthly and a SharePoint list of inspection completions by item containing the dates items are inspected.

I would like to build a data model that shows a visual of the current month with items not in the sharepoint list as red and items that have been inspected as green.  A donut chart that shows percentage of eyewash stations inspected..  and when we hover over the red we are shown a tooltip list of items needing inspected, or populate a table list we need to go check so that each month we ensure completion.

So my question is, what is the best way to cross reference month by month against a master list?  I can think of a way where I make a calculated column using today's month number and whether or not we have a match with the sharepoint, but is that the best way? and it doesn't work for historical records of past months.. 

In short, master excel list with itemID, and sharepoint list of itemID and inspected_date.  Did we inspect all items we were supposed to?

[ExcelMonke]

Hi there,

Based on your description all of this is very doable! What you want to achieve is maybe a bit too much to explain and write out for you in a single reply, however I would recommend you start with looking at some time intelligence articles (google Time Intelligence Power BI). 

One thing you will definitely want is a Date dimension table - SQLBI's date table works just fine (https://www.sqlbi.com/tools/dax-date-template/)

There you can do calculations such as 

Past_Inspection_Date = 
IF(Today()-InspectionList[InspectionDate]>31,0,1)

From there you can SUM all the "1" results and visualise. 

Regarding your point on historical data: This may be a case where you have to evaluate the structure of your dataset. Are you overwriting each date everytime an item is inspected? Or are you creating a new row for inspection? Although there is no true "correct" answer here, the latter is recommended as it will allow you to visualize historical trends, etc. 

Hope this helps you with somewhere to start!

[AspiringAnalyst]

Thank you for your reply!  A new row is added to the sharepoint list for each instance of an inspection.  And I for sure have used SQLBI's date table.  I'll start playing with your IF statement above.