Add rows for misisng years based on condition

[Anonymous]

Hello Folks,

I am trying to add rows based on certain condition. I need to add rows for missing YYYYMM for any specific MembershipID & Membership Group and fill down the AccountBalance. I am struck at adding rows based on the condition.

The source data will be incrementally updated, whenever a new entry is made, the missing months for that specific MembershipID & MembershipGroup needs to be identified and the rows are to be inserted by doing a filldown.

Any leads please?

Data:

MembershipID	InceptionDate(YYYYMM)	MembershipGroup	AccountBalance
1	202112	10	10000
1	202110	20	7000
2	202106	10	20000
3	201906	20	35000
3	202009	20	7500
1	202201	20	5000
2	202109	10	10000
Expected Output
1	202112	10	10000
1	202110	20	7000
1	202111	20	7000
1	202112	20	7000
1	202201	20	5000
2	202106	10	20000
2	202107	10	20000
2	202108	10	20000
2	202109	10	10000
3	201906	20	35000
3	201907	20	35000
3	201908	20	35000
3	201909	20	7500

[Anonymous]

Hi  @Anonymous ,

Here are the steps you can follow：

Table：

1. Create calculated column.

min = MINX(FILTER(ALL('Table'),'Table'[MembershipID]=EARLIER('Table'[MembershipID])),[InceptionDate(YYYYMM)])

max = MaxX(FILTER(ALL('Table'),'Table'[MembershipID]=EARLIER('Table'[MembershipID])),[InceptionDate(YYYYMM)])

Flag =
IF([InceptionDate(YYYYMM)]=[max],1,0)

2. Result:

Distinct_table：

1. Create calculated table.

Distinct_table = distinct(selectcolumns(CALENDAR(DATE(2019,1,1),DATE(2022,1,1)),"InceptionDate(YYYYMM)",value(YEAR([Date])&format([Date],"mm"))))

2. Create calculated column.

MembershipGroup =
CALCULATE(MAX('Table'[MembershipGroup]),FILTER(ALL('Table'),'Distinct_table'[InceptionDate(YYYYMM)]='Table'[min]))

AccountBalance = CALCULATE(MAX('Table'[AccountBalance]),FILTER(ALL('Table'),'Distinct_table'[InceptionDate(YYYYMM)]='Table'[min]))

Distinct_table = distinct(selectcolumns(CALENDAR(DATE(2019,1,1),DATE(2022,1,1)),"InceptionDate(YYYYMM)",value(YEAR([Date])&format([Date],"mm"))))

3. Result.

Finally_table：

1. Create calculated table.

finally_table =
SUMMARIZE(FILTER('Distinct_table',[MembershipID]<>BLANK()),'Distinct_table'[MembershipID],'Distinct_table'[InceptionDate(YYYYMM)])

2. Create calculated column.

MembershipGroup =
CALCULATE(MAX('Distinct_table'[MembershipGroup]),FILTER(ALL(Distinct_table),[MembershipID]=EARLIER(finally_table[MembershipID])))

AccountBalance =
var _1=
CALCULATE(MAX('Distinct_table'[AccountBalance]),FILTER(ALL(Distinct_table),[MembershipID]=EARLIER(finally_table[MembershipID])))
var _flag=
CALCULATE(MAX('Table'[Flag]),FILTER(ALL('Table'),[MembershipID]=EARLIER(finally_table[MembershipID])&&[InceptionDate(YYYYMM)]=EARLIER(finally_table[InceptionDate(YYYYMM)])))
var _2=
CALCULATE(SUM('Table'[AccountBalance]),FILTER(ALL('Table'),[MembershipID]=EARLIER(finally_table[MembershipID])&&[InceptionDate(YYYYMM)]=EARLIER(finally_table[InceptionDate(YYYYMM)])))
return
IF(_flag=1,_2,_1)

3. Result.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[freginier]

Hi 

You should create a second table to create continuous date. I haven't tried it but it should work.. 

Step 1 : Convert InceptionDate to date format YYYY/MM/DD (easier to get max and min)  

Step 2 : Duplicate your query

Step 3 : In the new query, use Group By function to group data on ID column (use all rows operation)

Step 4 : You will have table for each ID then display min and max value 

Step 5 : Create list in column to list date from min and max date. Use function like number.from

Step 6 : Expand new data 

Step 7 : Merge with your initial query and try to fill down

I hope it will work..

[Anonymous]

Hi freginier,

Thanks for your inputs. I am pretty new to DAX/Power Query. I have reached step 4 already. But, I couldn't figure out Step5. Could you please help me with the fuction I need to write and implement?

TIA. 🙂

[freginier]

In PowerQuery create new column with something like this 

{Number.From(Start)..Numer.From(End)}