Forum Discussion
Storing intermediate figures / month end snapshot
We have an issue log that uses a customized SharePoint list. Each issue has its registration date stored in a field, therefore it's possible to measure the amount of tickets created on a monthly basis via Power BI. Same thing goes for closed issues. However, there's a due date as well, which changes over the course of months (as the handling of an issue proceeds into a new step, the due date is updated). So there’s no way to measure the overdue deficiencies retroactively, which means a monthly summary of that data should be stored somehow. Currently this is done via a VBA script in an Excel file using a data connection, which has to be run manually. But how to do this fully automated in combination with Power BI?
I've looked into a couple of options (e.g. storing the data in an external Excel via, updating it via Power Automate), but none seemed to be suitable in the end. I don't have a possibility to store data using SQL servers and the likes. There are some topics here that discuss the same issue, but the solutions there are rather complex (custom Python or R scripts, subscribing to a paginated report and having Power Automate extract the data from an email) and do not really elaborate further. It doesn't seem like something completely out of scope for Power BI, but is it possible to be done in a proper, maintainable way?
Hi, Anonymous
As your due date will be updated, you can try to use the date slicer to determine the time.
For example, create a new calendar table with expiration time. You can also decide the start time and end time of the calendar table.
Date = CALENDAR ( MIN ( Findings[Next Step Due Date] ), MAX ( Findings[Next Step Due Date] ) )Measure:
Due date = MAX('Date'[Date])So you can turn the changed due date into the value selected by the slicer when writing the measure.
I hope my advice can help you.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
v-zhangtiCommunity Support
Hi, Anonymous
Can you provide some test data? It can be Excel or PBIX file. What is the expected result you want to achieve? It's best to show the picture, too. Please provide more details. Thank you.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This is how it looks in Power BI Desktop (only the columns relevant to the question):
We want to measure when items are overdue ('next step due date' < current date). I made a measure table to do this per site (location) audited:
...using the following formula:
Overdue Findings = SUMMARIZECOLUMNS(Findings[Site], FILTER( Findings, Findings[Step] <> "Closed" && Findings[Next Step Due Date] < TODAY()), "Qty", COUNT(Findings[Finding ID]))This does exactly want we want: get the current amount of overdue findings. However, we want capture these figures monthly / quarterly. This cannot be done "retroactively", as the due date is updated when the finding enters a new step/phase.So basically what we want to achieve is to make a snapshot of the data in the measure table in a scheduled and automatic way, so there's monthly data to perform analysis on. Is this possible in Power BI in a feasible way?- v-zhangti
Hi, Anonymous
As your due date will be updated, you can try to use the date slicer to determine the time.
For example, create a new calendar table with expiration time. You can also decide the start time and end time of the calendar table.
Date = CALENDAR ( MIN ( Findings[Next Step Due Date] ), MAX ( Findings[Next Step Due Date] ) )Measure:
Due date = MAX('Date'[Date])So you can turn the changed due date into the value selected by the slicer when writing the measure.
I hope my advice can help you.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.