Add rows for misisng years

[Anonymous]

Hello Folks,

I am trying to add rows based on certain condition.

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

[v-angzheng-msft · Accepted solution]

Hi, @Anonymous 

Create a calculation table as follows:

T = 
var _t=ADDCOLUMNS('Table',"maxTo",[_Max],"minTo",[_min])
var _id_to=SUMMARIZE(_t,'Table'[MembershipID],'Table'[MembershipGroup],[maxTo],[minTo])

var _id=SUMMARIZE('Table','Table'[MembershipID],'Table'[MembershipGroup])
var _maxOfAll=Value([_Max])
var _minOfAll=Value([_min])

var _GenerateTable=GENERATE(_id,GENERATESERIES(_minOfAll,_maxOfAll))
var _Fact=FILTER(_GenerateTable,[MembershipID]='Table'[MembershipID]&&[MembershipGroup]='Table'[MembershipGroup]&&
        ''[Value]<=MAXX(FILTER(_id_to,[MembershipID]=EARLIER([MembershipID])&&[MembershipGroup]=EARLIER([MembershipGroup])),Value([maxTo]))&&''[Value]>=MINX(FILTER(_id_to,[MembershipID]=EARLIER([MembershipID])&&[MembershipGroup]=EARLIER([MembershipGroup])),Value([minTo]))&&Value(RIGHT(CONVERT(''[Value],STRING),2))<=12&&Value(RIGHT(CONVERT(''[Value],STRING),2))>=1
        )

return _Fact

Create 2 measures to get the maximum year-month and the minimum year-month.

_Max = 
CALCULATE(MAX('Table'[InceptionDate(YYYYMM)]),ALLEXCEPT('Table','Table'[MembershipID],'Table'[MembershipGroup]))

_min = 
CALCULATE(MIN('Table'[InceptionDate(YYYYMM)]),ALLEXCEPT('Table','Table'[MembershipID],'Table'[MembershipGroup]))

Finally create a calculated column to get the result.

Account = 
var _account=
LOOKUPVALUE('Table'[AccountBalance],'Table'[MembershipID],[MembershipID],'Table'[MembershipGroup],[MembershipGroup],'Table'[InceptionDate(YYYYMM)],CONVERT([Value],STRING))
var _table=
FILTER(ALL('Table'),
        'Table'[MembershipID]=EARLIER(T[MembershipID])
        &&'Table'[MembershipGroup]=EARLIER(T[MembershipGroup])
        &&Value('Table'[InceptionDate(YYYYMM)])<=EARLIER([Value])
        )
var _fillDown=
CALCULATE(
        Min('Table'[AccountBalance]),
        _table
    )
var _ifBlank=IF(ISBLANK(_account),_fillDown,_account)

return _ifBlank

Result:

Please refer to the attachment below for details. Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

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

[jennratten]

This is possible but are you sure you'd like to add rows to your dataset?  If the goal is to show balances for x items as of a certian date, there are ways to handle this with a dax measure so that you don't have to increase your dataset size.

[Anonymous]

Thanks for giving me the heads-up of expanding my data set. I was strangled in the thought I would be able to acheieve only by expanding the data set and then displaying it in the visuals.

It would be helpful for me if I could acheieve without expanding my dataset. However, I am prepared for both the ways. Please let me know

Thanks!

[jennratten]

Great - it would be best not to add more rows if they are not needed.  You will need to add a date column to your data table.  Change #"Prior Step" in the script below to the actual prior step name for your table.  You may also need to edit the column names.  In the sample data you provided there were extra trailing spaces that may or may not be present in your actual data table.

Table.AddColumn(#"Prior Step", "Date", each Date.From ( Text.Combine({Text.Middle(Text.From([#"InceptionDate(YYYYMM)    "], "en-US"), 4), "/1/", Text.Start(Text.From([#"InceptionDate(YYYYMM)    "], "en-US"), 4)})), Date.Type)

Next, add a date table.  Create a blank query then open the advanced editor and replace the contents with this script.

let
    Source = #date(2021,1,1),
    Custom1 = List.Dates(Source, Number.From(DateTime.LocalNow())- Number.From(Source) ,#duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Custom1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
    #"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
    #"Inserted Start of Month" = Table.AddColumn(#"Renamed Columns1", "Start of Month", each Date.StartOfMonth([Date]), type date)
in
    #"Inserted Start of Month"

Load both tables to the data model.  Since both tables include a column named Date, Power BI should automatically create a relationship between the two tables.  Make sure this is a one (date table) to many (data table) relationship.  Go to the table view and make sure the date field in both tables are formatted the same way (if not, update the formatting so they match).

On your visual, add your measures (or columns from the data table) as well as one of columns from the date table.

[AlexisOlson]

Yes. In particular, having a proper date dimension table to work with should eliminate the need to expand your dataset