Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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 stru...
Anonymous's avatar
Anonymous
Not applicable
4 years ago

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