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

     

tamerj1's avatar
tamerj1
Community Champion
4 years ago

Hi kostask 
In order to obtain correct totals please follow updated sample file https://we.tl/t-W0y5fERFnF

The new measure refers to the original measure using switch statement

 

Closing Balance = 
VAR CurrentDate = MAX ( 'Date'[Date] )
VAR CurrentValue = SELECTEDVALUE ( Sheet1[Value] )
VAR CurrentAcountTable = CALCULATETABLE ( Sheet1, ALLEXCEPT ( Sheet1, Sheet1[Account] ) )
VAR PreviousDatesTable = FILTER ( CurrentAcountTable, Sheet1[Date] < CurrentDate )
VAR PreviousDate = MAXX ( PreviousDatesTable, Sheet1[Date] )
VAR PreviousDateTable = FILTER ( PreviousDatesTable, Sheet1[Date] = PreviousDate )
VAR PreviousValue = MAXX ( PreviousDateTable, Sheet1[Value] )
RETURN
    COALESCE ( CurrentValue, PreviousValue )
Total Colsing Balance = 
SWITCH (
    TRUE ( ),
    ISINSCOPE ( Sheet1[Account] ) && ISINSCOPE ( 'Date'[Date] ), [Closing Balance],
    ISINSCOPE ( Sheet1[Account] ), SUMX ( VALUES ( 'Date'[Date] ), [Closing Balance] ),
    SUMX ( CROSSJOIN ( VALUES ( 'Date'[Date] ), ALL ( Sheet1[Account] ) ), [Closing Balance] )
)