Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Add rows for misisng years

Hello Folks,   I am trying to add rows based on certain condition.   Any leads please?   Data: MembershipID     InceptionDate(YYYYMM)     MembershipGroup      AccountBalance    1 202...
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    4 years ago

    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's avatar
jennratten
Super User
4 years ago

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.

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      jennratten
      Super User
      4 years ago

      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.