Forum Discussion

Helper II

4 years ago

Solved

Calculating dimension for missing dates

Hi, guys! I came up with a problem that has to do with bank statements and the available totals per account per date. I have a table (A) that describes the closing balance per account per date. ...

tamerj1

4 years ago

HI kostask
Please refer to sample file with the solution https://we.tl/t-qqmQwks0kv

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 )

tamerj1

Community Champion

4 years ago

HI kostask
Please refer to sample file with the solution https://we.tl/t-qqmQwks0kv

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 )