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 )
kostask's avatar
kostask
Helper II
4 years ago

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