Report changes from historical data

[Anonymous]

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!

[v-jingzhang · Accepted solution]

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.