Forum Discussion
Add rows for misisng years based on condition
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 |
4 Replies
freginierSolution Sage
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..
- freginier
In PowerQuery create new column with something like this
{Number.From(Start)..Numer.From(End)}
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