Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 )
Hi, @tamerj1
The raw data on the final table have exactly the form you can see on the matrix (table A)
Thanx
Kostas
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 )
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
@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
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?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
56 | |
27 | |
23 | |
15 | |
11 |
User | Count |
---|---|
76 | |
58 | |
46 | |
17 | |
12 |