Forum Discussion

kostask's avatar
kostask
Helper II
4 years ago
Solved

Filling values for missing dates for many attribute combinations

Hi guys. I had the community's support for a similar problem, but the specific one seems more complicated for me to resolve. So, we have 3 attributes (Bank, CustomerNo, AccountNo), which give many ...
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi kostask 
    Here is the updated solution with correct subtotals https://we.tl/t-ULVbpV6vB9

     

    FinalBalance1 = 
VAR CurrentDate =
    MAX ( 'Calendar'[Date] )
VAR IterationTable =
    CALCULATETABLE (
        SUMMARIZE ( 'Totals', 'Totals'[Bank], 'Totals'[Company], 'Totals'[AccountNo] ),
        ALL ( 'Calendar' )
    )
RETURN
    SUMX (
        IterationTable,
        CALCULATE (
            VAR CurrentValue =
                SUM ( 'Totals'[FinalBalance] )
            VAR CurrentAcountTable =
                CALCULATETABLE ( 'Totals', ALL ('Calendar' ) )
            VAR PreviousDatesTable =
                FILTER ( CurrentAcountTable, 'Totals'[Date] < CurrentDate )
            VAR PreviousDate =
                MAXX ( PreviousDatesTable, 'Totals'[Date] )
            VAR PreviousDateTable =
                FILTER ( PreviousDatesTable, 'Totals'[Date] = PreviousDate )
            VAR PreviousValue =
                SUMX ( PreviousDateTable, 'Totals'[FinalBalance] )
            RETURN
                - COALESCE ( CurrentValue, PreviousValue )
        )
    )

     

v-jianboli-msft's avatar
v-jianboli-msft
Community Support
4 years ago

Hi kostask ,

 

I did two ways to create a new table

  1. Using DAX

 

Table 2 =
var _t= SUMMARIZE('A',[Bank],[AccountNo],[CustomerNo],"Min",MIN('A'[Date]),"Max",MAX('A'[Date]))

var _t2=  CROSSJOIN( _t , CALENDAR(MIN('A'[Date]),MAX('A'[Date])))

return FILTER( _t2,[Date]>=[Min] && [Date]<=[Max])

 

  1. In Power Query：

Group by --> Add custom column -->Expand and Change type

 

 

Output:

 

 

Here is the M code:

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("fdA9CsAgDAXgu2QuNEm1nVtXQScRpPe/RlMjFLE4+CTw8fwpBWg9VkZmWOCURYgoW85ZB7iXAmZmWM3WG1bjnNPhz7SelJLk1oyd9BANPdfXE0KQtEr2KZmZGON7rq2EetJu472XNOOrBlN/8H4A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Bank = _t, CustomerNo = _t, AccountNo = _t, Balance = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"Date", type date}}, "en-GB"),
    #"Changed Type" = Table.TransformColumnTypes(#"Changed Type with Locale",{{"Bank", type text}, {"CustomerNo", Int64.Type}, {"AccountNo", type text}, {"Balance", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Bank", "CustomerNo", "AccountNo"}, {{"Max", each List.Max([Date]), type nullable date}, {"Min", each List.Min([Date]), type nullable date}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each {Number.From([Min])..Number.From([Max])}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}})
in
    #"Changed Type1"

 

When the table is created, add a new column:

 

Balance = CALCULATE(MAX('A'[Balance]),FILTER('A',[Bank]=EARLIER('New Table'[Bank]) && [AccountNo]=EARLIER('New Table'[AccountNo]) && [CustomerNo]=EARLIER('New Table'[CustomerNo]) && [Date]<=EARLIER('New Table'[Date])))

 

Final output:

 

Best Regards,

Jianbo Li

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