Forum Discussion
Fill in missing months with prior values
I am working with a history table which captures the details for each record whenever that record is changed. This results in multiple rows for each record for each month. I have worked it out such that each month reflects the record as of the max change for that month. However, if no change was made in a month, then there is no row for that record for that month. I would like to find a way, in those cases, to generate a record for the missing month with the values from the last month where the record was populated. In the below image, you can see there is no row for June 2020, Oct 2020, Dec 2020, May 2021. How would I add rows for those months populating wtih the probablity from May 2020, Sept 2020, Nov 2020 and Apr 2021, respectively?
Hi Anonymous ,
According to your description, you will need a new Calendar table with the lastest day of each month based on the date period, and then crossjoin with all distinct OpportunityIds:
New Table = VAR _cal = ADDCOLUMNS ( CALENDAR ( MIN ( 'Table'[As of Month] ), MAX ( 'Table'[As of Month] ) ), "Year", YEAR ( [Date] ), "Month", MONTH ( [Date] ) ) VAR _dates = SUMMARIZE ( _cal, [Year], [Month], "Last Day of Month", MAXX ( FILTER ( _cal, [Year] = EARLIER ( [Year] ) && [Month] = EARLIER ( [Month] ) ), [Date] ) ) VAR _t1 = VALUES ( 'Table'[OpportunityId] ) VAR _t2 = SELECTCOLUMNS ( _dates, "As of Month", [Last Day of Month] ) RETURN CROSSJOIN ( _t1, _t2 )Then to find the matched Probability:
Filled Probability = VAR _lastDate = CALCULATE ( MAX ( 'Table'[As of Month] ), FILTER ( 'Table', [OpportunityId] = EARLIER ( 'New Table'[OpportunityId] ) && [Probability] <> BLANK () && [As of Month] <= EARLIER ( 'New Table'[As of Month] ) ) ) RETURN LOOKUPVALUE ( 'Table'[Probability], [OpportunityId], [OpportunityId], [As of Month], _lastDate )Final output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Hi Anonymous ,
According to your description, you will need a new Calendar table with the lastest day of each month based on the date period, and then crossjoin with all distinct OpportunityIds:
New Table = VAR _cal = ADDCOLUMNS ( CALENDAR ( MIN ( 'Table'[As of Month] ), MAX ( 'Table'[As of Month] ) ), "Year", YEAR ( [Date] ), "Month", MONTH ( [Date] ) ) VAR _dates = SUMMARIZE ( _cal, [Year], [Month], "Last Day of Month", MAXX ( FILTER ( _cal, [Year] = EARLIER ( [Year] ) && [Month] = EARLIER ( [Month] ) ), [Date] ) ) VAR _t1 = VALUES ( 'Table'[OpportunityId] ) VAR _t2 = SELECTCOLUMNS ( _dates, "As of Month", [Last Day of Month] ) RETURN CROSSJOIN ( _t1, _t2 )Then to find the matched Probability:
Filled Probability = VAR _lastDate = CALCULATE ( MAX ( 'Table'[As of Month] ), FILTER ( 'Table', [OpportunityId] = EARLIER ( 'New Table'[OpportunityId] ) && [Probability] <> BLANK () && [As of Month] <= EARLIER ( 'New Table'[As of Month] ) ) ) RETURN LOOKUPVALUE ( 'Table'[Probability], [OpportunityId], [OpportunityId], [As of Month], _lastDate )Final output:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.