Populating data for missing months based on previous month

[Anonymous]

Hi folks,

I have a table called “Status Report” that shows a time-stamp of each time the status of a project was changed.

Example:

I we need to show is a monthly status report – that is – what was the “Current status” of each project every month.

The challenge is that the status change date only gets populated when the status actually changes.

For example, Project A was Red in Jan 2020, and then it was changed to Yellow in Mar 2020. But that means it was still Red in Feb 2020, but that data is not populated. And then from Yellow, it went to Green in May 2020, which means it was Yellow in April 2020 as well. And then it stayed Green up until Jul 2020. Final product could be something like this (the month in which the status was changed is marked as Red for your reference, while the rest are missing months where status is based on the previous month's status):

I need a way to calculate that missing data so that we can show what the status was each month (not only when the status was changed)

Do you have any suggestions?

[v-diye-msft · Accepted solution]

Hi @Anonymous 

Kindly refer to my below results:

Pbix attached.