cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lotus22
Helper III
Helper III

Need support with filling empty months of Data with previous months

I have following data for each division. We do an assessment almost every six months for each division. For example, Division 1 is done in Jan 2021. They have their score of 3. I only get data for that month when the assessment is completed (in this case Jan 2021). I need to carry over this data for subsequent months and show monthly on chart even though Feb, Mar, Apr, May, Jun are empty for Division 1. After July, the new assessment takes over for subsequent months until future assessment. How can do this with the data provided.

 

 

DivisionNameDateMonthMMSAScoreDivisionScore
Division 11/30/2021Jan-213.50819672MSAH3
Division 21/31/2021Jan-214.14754098Acuna4
Division 32/1/2021Feb-213.78688525Sabinas3
Division 42/2/2021Feb-213.47540984Allende3
Division 52/3/2021Feb-213.36065574CSN3
Division 62/4/2021Feb-213.80327869ESSS3
Division 73/2/2021Mar-214.2295082QSS4
Division 83/3/2021Mar-213.36065574LVSS3
Division 93/2/2021Mar-212.43333333MSC2
Division 103/3/2021Mar-213.08196721Puebla3
Division 17/30/2021Jul-213.50819672MSAH4
Division 27/31/2021Jul-214.14754098Acuna5
Division 38/1/2021Aug-213.78688525Sabinas2
Division 48/2/2021Aug-213.47540984Allende4
Division 58/3/2021Aug-213.36065574CSN4
Division 68/4/2021Aug-213.80327869ESSS3
Division 79/1/2021Sep-214.2295082QSS5
Division 89/2/2021Sep-213.36065574LVSS4
Division 99/3/2021Sep-212.43333333MSC4
Division 109/4/2021Sep-213.08196721Puebla5
3 ACCEPTED SOLUTIONS
Greg_Deckler
Super User
Super User

@lotus22 Couple different ways of doing this. If you have a disconnected calendar table that you use for your axis for example you could solve this in a measure by simply "looking up" the last value. So if it is April, you would "lookup" March. You could use MINX(ALL('Table'),[Date]) for example. That would likely be the cleanest solution otherwise you will have to invent rows using something like UNION or GENERATESERIES, etc.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

View solution in original post

@Greg_Deckler  I do have a disconnected calendar :-). How would have accomplish the data set with missing months?

 

lotus22_0-1636393762935.png

 

View solution in original post

@lotus22 Maybe something like below. I attached a PBIX below sig. You want Page 4.

Measure 13 = 
    VAR __Date = MAX('Calendar'[Date])
    VAR __Division = MAX('Table10'[DivisionName])
    VAR __LastDate = MAXX(FILTER('Table10',[Date]<=__Date && [DivisionName] = __Division),[Date])
    VAR __LastScore = MAXX(FILTER('Table10',[Date]=__LastDate && [DivisionName] = __Division),[Score])
RETURN
    __LastScore

 


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

@lotus22 Couple different ways of doing this. If you have a disconnected calendar table that you use for your axis for example you could solve this in a measure by simply "looking up" the last value. So if it is April, you would "lookup" March. You could use MINX(ALL('Table'),[Date]) for example. That would likely be the cleanest solution otherwise you will have to invent rows using something like UNION or GENERATESERIES, etc.


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

@Greg_Deckler  I do have a disconnected calendar :-). How would have accomplish the data set with missing months?

 

lotus22_0-1636393762935.png

 

@lotus22 Maybe something like below. I attached a PBIX below sig. You want Page 4.

Measure 13 = 
    VAR __Date = MAX('Calendar'[Date])
    VAR __Division = MAX('Table10'[DivisionName])
    VAR __LastDate = MAXX(FILTER('Table10',[Date]<=__Date && [DivisionName] = __Division),[Date])
    VAR __LastScore = MAXX(FILTER('Table10',[Date]=__LastDate && [DivisionName] = __Division),[Score])
RETURN
    __LastScore

 


@ me in replies or I'll lose your thread!!!
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Learn Power BI 2nd Edition

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May 23 2022 episode 5 with aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt will talk about the importance of Data Modeling.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!