Forum Discussion

kostask's avatar
kostask
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's avatar
    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 )
Greg_Deckler's avatar
Greg_Deckler
Community Champion
4 years ago

kostask You could do a calculated column or measure where you check if the value is blank and if not grab the previous non-blank value. However, I would recommend that if your data looks as posted that you first un-pivot your account columns.

 

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column = 
  VAR __Current = [Value]
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous