Forum Discussion
Transactional data progress over time
Hello,
I would like to understand how I can best save data from a transactional report over time to build a progression report out of this data.
In more detail:
I get a report on a daily basis which shows me how many orders are shipped, invoiced etc. This data obviously changes day by day as orders change from e.g. "not shipped" to "shipped" and from "not invoiced" to "invoiced".
Apart from showing absolute numbers in a PowerBI dashboard (e.g. "number of invoice pending orders"), I also want to show a daily progression, e.g. "orders invoiced since yesterday". With this bit, I am struggling on how to set this up in PBI or PowerQuery.
I have to say that I am PBI beginner and there might be an obvious solution to this challenge....happy to learn anyhow and I appreciate any kind of support.
Thanks!
Hi, andi2333
You can use Power Query to import daily reports. If the reports are in folders, Power BI can automatically merge them into a single dataset. Make sure that each report has a date column to determine when the data was reported. If not, you may need to add it manually or infer it from the file metadata. You can check out the following links:
Power Query Folder connector - Power Query | Microsoft Learn
How to Store Historical Data in Power BI - Zebra BI
Power query: how to merge daily data with historical without creating - Microsoft Community
You can create calculated columns for daily progress, and once you have a combined dataset, you can create calculated columns in Power Query or DAX to identify daily changes. For example, to track "Orders invoiced since yesterday", you can create a calculated column that compares the invoice status of orders for two consecutive days.
To ensure that your dashboard reflects the most up-to-date data, set up a scheduled refresh in Power BI. This will automatically update the dataset and visuals based on new reports received each day.
Configure scheduled refresh - Power BI | Microsoft Learn
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 Reply
Hi, andi2333
You can use Power Query to import daily reports. If the reports are in folders, Power BI can automatically merge them into a single dataset. Make sure that each report has a date column to determine when the data was reported. If not, you may need to add it manually or infer it from the file metadata. You can check out the following links:
Power Query Folder connector - Power Query | Microsoft Learn
How to Store Historical Data in Power BI - Zebra BI
Power query: how to merge daily data with historical without creating - Microsoft Community
You can create calculated columns for daily progress, and once you have a combined dataset, you can create calculated columns in Power Query or DAX to identify daily changes. For example, to track "Orders invoiced since yesterday", you can create a calculated column that compares the invoice status of orders for two consecutive days.
To ensure that your dashboard reflects the most up-to-date data, set up a scheduled refresh in Power BI. This will automatically update the dataset and visuals based on new reports received each day.
Configure scheduled refresh - Power BI | Microsoft Learn
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.