Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

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 ...
Need Help
  • v-jingzhang's avatar
    v-jingzhang
    3 years ago

    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.

v-jingzhang's avatar
v-jingzhang
Community Support
3 years ago

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.