Forum Discussion
Report changes from historical data
Hello everyone,
I have set up a dataset in Power BI that every week import the same list of projects from an external file and saves the import date. Something like the table below:
| Import Date | Project ID | Expected Finish Date |
| 1-Jan | A | 15-Feb |
| 1-Jan | B | 30-Mar |
| 1-Jan | C | 25-Feb |
| 8-Jan | A | 15-Feb |
| 8-Jan | B | 10-Apr |
| 8-Jan | C | 25-Feb |
| 8-Jan | D | 14-Apr |
| 15-Jan | A | 1-Mar |
| 15-Jan | B | 10-Apr |
| 15-Jan | C | 20-Mar |
| 15-Jan | D | 14-Apr |
I would like to plot the delays per project over time. Therefore getting a new table that looks like:
| CHANGE | 8-Jan | 15-Jan |
| A | 0 | 14 |
| B | 11 | 0 |
| C | 0 | 23 |
| D | NEW | 0 |
And a consequent graph like
What do you think it's the best/easiest way to achieve this?
Please consider that I am still very new in Power BI.
Thanks!
Hi Anonymous
You can add a calculated column with the following formula in the original table.
Delay Days = VAR _lastFinishDate = MAXX(FILTER('historical data','historical data'[Project ID]=EARLIER('historical data'[Project ID])&&'historical data'[Import Date]=EARLIER('historical data'[Import Date])-7),'historical data'[Expected Finish Date]) RETURN DATEDIFF(_lastFinishDate,'historical data'[Expected Finish Date],DAY)Then add above new column to Y-axis of a Stacked column chart. Use "Import Date" on X-axis and "Project ID" as Legend.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
1 Reply
Hi Anonymous
You can add a calculated column with the following formula in the original table.
Delay Days = VAR _lastFinishDate = MAXX(FILTER('historical data','historical data'[Project ID]=EARLIER('historical data'[Project ID])&&'historical data'[Import Date]=EARLIER('historical data'[Import Date])-7),'historical data'[Expected Finish Date]) RETURN DATEDIFF(_lastFinishDate,'historical data'[Expected Finish Date],DAY)Then add above new column to Y-axis of a Stacked column chart. Use "Import Date" on X-axis and "Project ID" as Legend.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.