Forum Discussion
How do I store and report historical data values (Intune compliance)
Morning
I have created a link to Microsoft Graph to query Intune data on device compliance as well as Entra ID users.
This provides me with a table of all devices, which I then filter to Windows only.
I have added a few calculations to theese tables, total qty of enabled employees, external accounts, Windows devices, compliant Windows devices, non-compliant Windows devices etc.
This dashboard refreshed automaticaly overnight, showing the previous days data.
I would like to be able to track compliance over time, taking the values from say the 1st of each month and then being able to generate a ongoing compliance dashboard showing the trend for the past 6 months.
I have no idea how to approach this and searching has not provided any answers, or even sufficient hints to research.
I assume I need to create a new table to store the values on the 1st, along with a date, no idea how to do this though.
Any advice, suggestions or help gratefuly appreciated 🙂
Hi Blue407
To store and report historical values (like Intune compliance) and track trends over time in Power BI, the best approach is to create periodic "snapshots" of your data, saving those snapshot values along with timestamps in a dedicated table. This enables trend analysis on compliance over months.
Suggested approach:
- Create a separate data storage (e.g. SQL table, SharePoint list, or CSV files in OneDrive/SharePoint) where compliance data snapshots are saved on scheduled intervals (e.g. monthly on the 1st).
- You can automate snapshot capture using Power Automate flows to export current data periodically and append it to your historical store.
- In Power BI, connect to this historical dataset and create measures/tables that track compliance over time.
- Use line charts or other trend visuals in Power BI dashboards to show compliance metrics for the last 6 months or desired period.
- Incremental refresh in Power BI can be used if your historical data storage is in a database to efficiently refresh only new snapshots.
- Alternatively, you can consider a manual snapshot approach if automation is not feasible, export data monthly and load it into a dedicated historical table.
This method ensures you have a reliable historical record of compliance without losing previous days data, enabling ongoing compliance tracking and reporting.
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!
3 Replies
Hi Blue407
To store and report historical values (like Intune compliance) and track trends over time in Power BI, the best approach is to create periodic "snapshots" of your data, saving those snapshot values along with timestamps in a dedicated table. This enables trend analysis on compliance over months.
Suggested approach:
- Create a separate data storage (e.g. SQL table, SharePoint list, or CSV files in OneDrive/SharePoint) where compliance data snapshots are saved on scheduled intervals (e.g. monthly on the 1st).
- You can automate snapshot capture using Power Automate flows to export current data periodically and append it to your historical store.
- In Power BI, connect to this historical dataset and create measures/tables that track compliance over time.
- Use line charts or other trend visuals in Power BI dashboards to show compliance metrics for the last 6 months or desired period.
- Incremental refresh in Power BI can be used if your historical data storage is in a database to efficiently refresh only new snapshots.
- Alternatively, you can consider a manual snapshot approach if automation is not feasible, export data monthly and load it into a dedicated historical table.
This method ensures you have a reliable historical record of compliance without losing previous days data, enabling ongoing compliance tracking and reporting.
🌟 I hope this solution helps you unlock your Power BI potential! If you found it helpful, click 'Mark as Solution' to guide others toward the answers they need.
💡 Love the effort? Drop the kudos! Your appreciation fuels community spirit and innovation.
🎖 As a proud SuperUser and Microsoft Partner, we’re here to empower your data journey and the Power BI Community at large.
🔗 Curious to explore more? [Discover here].
Let’s keep building smarter solutions together!
Hi Blue407,
I would also take a moment to thank grazitti_sapna , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Regards,
Community support Team.Hi Blue407,
I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We are always here to support you.
Regards,
Community support Team.
