Need support with filling empty months of Data with previous months

[lotus22]

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.

DivisionName	Date	Month	MMSAScore	Division	Score
Division 1	1/30/2021	Jan-21	3.50819672	MSAH	3
Division 2	1/31/2021	Jan-21	4.14754098	Acuna	4
Division 3	2/1/2021	Feb-21	3.78688525	Sabinas	3
Division 4	2/2/2021	Feb-21	3.47540984	Allende	3
Division 5	2/3/2021	Feb-21	3.36065574	CSN	3
Division 6	2/4/2021	Feb-21	3.80327869	ESSS	3
Division 7	3/2/2021	Mar-21	4.2295082	QSS	4
Division 8	3/3/2021	Mar-21	3.36065574	LVSS	3
Division 9	3/2/2021	Mar-21	2.43333333	MSC	2
Division 10	3/3/2021	Mar-21	3.08196721	Puebla	3
Division 1	7/30/2021	Jul-21	3.50819672	MSAH	4
Division 2	7/31/2021	Jul-21	4.14754098	Acuna	5
Division 3	8/1/2021	Aug-21	3.78688525	Sabinas	2
Division 4	8/2/2021	Aug-21	3.47540984	Allende	4
Division 5	8/3/2021	Aug-21	3.36065574	CSN	4
Division 6	8/4/2021	Aug-21	3.80327869	ESSS	3
Division 7	9/1/2021	Sep-21	4.2295082	QSS	5
Division 8	9/2/2021	Sep-21	3.36065574	LVSS	4
Division 9	9/3/2021	Sep-21	2.43333333	MSC	4
Division 10	9/4/2021	Sep-21	3.08196721	Puebla	5

[Greg_Deckler · Accepted solution]

@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.

[lotus22 · Accepted solution]

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

[Greg_Deckler · Accepted solution]

@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.

[lotus22 · Accepted solution]

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

[Greg_Deckler · Accepted solution]

@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