cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Populating data for missing months based on previous month

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:

sr1.JPG
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):
am1990_1-1593705671099.png

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Populating data for missing months based on previous month

Hi @am1990 

 

Kindly refer to my below results:

2.PNG

Pbix attached.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

1 REPLY 1
Highlighted
Community Support
Community Support

Re: Populating data for missing months based on previous month

Hi @am1990 

 

Kindly refer to my below results:

2.PNG

Pbix attached.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

July 2020 Community Highlights

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors