topic Re: Calculating dimension for missing dates in DAX Commands and Tips

Calculating dimension for missing dates

kostask — Mon, 11 Jul 2022 13:50:33 GMT

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

Re: Calculating dimension for missing dates

tamerj1 — Mon, 11 Jul 2022 14:03:36 GMT

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?

Re: Calculating dimension for missing dates

Greg_Deckler — Mon, 11 Jul 2022 14:04:13 GMT

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

Re: Calculating dimension for missing dates

kostask — Mon, 11 Jul 2022 14:18:27 GMT

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

Re: Calculating dimension for missing dates

kostask — Mon, 11 Jul 2022 14:24:30 GMT

Hi, tamerj1

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

Thanx

Kostas

Re: Calculating dimension for missing dates

tamerj1 — Mon, 11 Jul 2022 15:22:35 GMT

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 )

Re: Calculating dimension for missing dates

kostask — Tue, 12 Jul 2022 07:13:40 GMT

Hi tamerj1

Really great work! Works fine

Thanks a lot!

Kostas