topic Add rows for misisng years based on condition in DAX Commands and Tips

Add rows for misisng years based on condition

Anonymous — Thu, 06 Jan 2022 06:47:27 GMT

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

Re: Add rows for misisng years based on condition

freginier — Fri, 07 Jan 2022 13:26:37 GMT

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

Re: Add rows for misisng years based on condition

Anonymous — Fri, 07 Jan 2022 14:23:07 GMT

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

Re: Add rows for misisng years based on condition

freginier — Fri, 07 Jan 2022 15:15:15 GMT

In PowerQuery create new column with something like this

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

Re: Add rows for misisng years based on condition

Anonymous — Mon, 10 Jan 2022 07:58:04 GMT

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