Calculating dimension for missing dates

[kostask]

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. When there is no transaction, the dates are missing (ex 3/7 for account A, or 4/7 for both accounts A and B ), so the closing balance has to be of the previous - latest day with record (ex 200 for 3/7 and 4/7 for account A)

The final outcome must be a graph that will show for both accounts (and separately) the available balance for all dates, including the missing ones (table b - cells in yellow backround)

I guess for sure we need a date-table, but after many things I tried,  I didn't manage to have the result

Any help would be highly appreciated!

Thank You

Kostas

[kostask]

Hi, @tamerj1 

The raw data on the final table have exactly the form you can see on the matrix (table A)

Thanx

Kostas

[tamerj1 · Accepted solution]

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 )

[kostask]

Hi @tamerj1 

Really great work! Works fine

Thanks a lot!

Kostas

[kostask]

Hi @Greg_Deckler 

Totally agree with the "unpivoting"

As you can see in the table, my problem is that there are completely missing dates (ex 4/7/2022), for which I want to show results to the final outcome,  so I think  I can't work based on the dates of the main table (if i' m correct) (?)

Thanx a lot

[Greg_Deckler]

@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/3395....
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

[tamerj1]

Hi @kostask 
How does the raw data look like? My Undestanding is that table A is a table or matrix visual. Can share the raw data of the same sample as copy/paste?