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

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

6 Replies

  • kostask's avatar
    kostask
    Helper II
    4 years ago

    Hi, tamerj1 

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

     

    Thanx

    Kostas

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

    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?

  • 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

     

  • 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