Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello
I have a data set with balance sheet values.
My table is like this:
Account | Date | Balance End of month | |
1 | 31/01/2019 | 100 | |
2 | 31/01/2019 | 150 | |
3 | 31/01/2019 | 60 | |
1 | 28/02/2019 | 150 | |
3 | 28/02/2019 | 120 | |
2 | 31/03/2019 | 500 | |
1 | 30/04/2019 | 200 | |
4 | 30/06/2019 | 1000 |
So I have the balance at the end of month for the accounts. The problem is that not all accounts have balance in all months (for example, for some accounts that did not have any transactions in a specified month).
I need to show something like this, if the time slicer is in june/2019 for example;
Account | Balance |
1 | 200 |
2 | 500 |
3 | 120 |
4 | 1000 |
I need to show the balance for all accounts - for a month in a time slicer.
However, if the account does not have balance in the choosen month, the data does not appear (and the balance sheet report is wrong). Tried many approaches from this forum, but none solved my specific situation
Any help is appreciated! Thanks a lot!
Solved! Go to Solution.
Total Balance =
var __lastVisibleDate = max( 'Calendar'[Date] )
VAR __accountsWithDates =
calculatetable (
addcolumns (
summarize (
Balances,
Accounts[AccountId]
),
"LastDate",
calculate (
max( Balances[Date] )
)
),
'Calendar'[Date] <= __lastVisibleDate
)
VAR __accountsWithDatesWithLineage =
TREATAS (
__accountsWithDates,
'Account'[AccountId],
'Calendar'[Date]
)
VAR __result =
CALCULATE (
sum ( Balances[Balance] ),
__accountsWithDatesWithLineage
)
RETURN
__result
This version might be faster than the one I posted before (it returns the same results, of course, but does not use iteration). Bear in mind that you should have a Calendar---a proper Date table---connected to the field Date in Balances. Also, you should only slice and dice by dimensions and the fact table's columns must be hidden. Only measures can be visible in the fact table.
Best
D
Total Balance =
var __lastVisibleDate = max( 'Calendar'[Date] )
VAR __accountsWithDates =
calculatetable (
addcolumns (
summarize (
Balances,
Accounts[AccountId]
),
"LastDate",
calculate (
max( Balances[Date] )
)
),
'Calendar'[Date] <= __lastVisibleDate
)
VAR __accountsWithDatesWithLineage =
TREATAS (
__accountsWithDates,
'Account'[AccountId],
'Calendar'[Date]
)
VAR __result =
CALCULATE (
sum ( Balances[Balance] ),
__accountsWithDatesWithLineage
)
RETURN
__result
This version might be faster than the one I posted before (it returns the same results, of course, but does not use iteration). Bear in mind that you should have a Calendar---a proper Date table---connected to the field Date in Balances. Also, you should only slice and dice by dimensions and the fact table's columns must be hidden. Only measures can be visible in the fact table.
Best
D
Man, thanks A LOT! It was exactly that. I need to study more about creating variables. I did only basic /intermediate courses about DAX and get a little lost some times.
// Say you have a Calendar connected to Date
// in Balances (or it could be disconnected as well).
// You also have an Accounts dimension
// that stores the unique Accounts. And you
// have a fact table that stores the Balances
// at the end of the months. As you say, some
// accounts will not have entries for some
// months. Accounts is connected to Balances
// via AccountID (1:*). Slicing can only be
// done via dimensions. All columns in the
// fact must be hidden.
// balance when only 1 account is visible
[_Balance41Account] = // hidden measure
var __oneAccountVisible = hasonevalue( Accounts[AccountID] )
var __maxDate = max( Calendar[Date] )
var __maxDateOfBalance =
calculate(
MAX( Balances[Date] ),
Balances[Date] <= __maxDate,
ALL( 'Calendar' )
)
var __balance =
calculate(
values( Balances[Balance] ),
Balances[Date] = __maxDateOfBalance,
ALL( 'Calendar' )
)
return
if( __oneAccountVisible, __balance )
// Final measure - visible
[Total Balance] = // works OK for any selection of accounts
sumx(
values( Accounts[AccountId] ),
[_Balance41Account]
)
There is a version that does not use iteration - check my post after this one. The second version should be faster on big tables.
Best
D
first, create a calendar table with last month days
CalendarTable = FILTER(ADDCOLUMNS(calendar(date(2019,1,1),date(2019,12,31)),"EndMonth",IF(EOMONTH([Date],0) = [Date],TRUE(),FALSE())),[EndMonth]=true())
then add Date field from calendar table to slicer and create a measure in your source table
Measure =
var _slicerDate = if(ISFILTERED(CalendarTable[Date]),CALCULATE(MAX(CalendarTable[Date])),today())
return
CALCULATE(MAX('Table'[Balance End of month]),'Table'[Date]<=_slicerDate)
Hello, thanks for the reply.
Forgot to explain something:
In the report I have titles, and need to return subtotals, like this:
CURRENT LIABILITIES (Sum of Values)
Account 1 (Value)
Account 2 (Value)
NONCURRENT LIABILITIES (Sum of Values)
Account 3 (Value)
I tried this, however if I use MAX, it will also show the MAX value on the subtotals, instead of showing the sum. I need to show not the MAX value of Balance End of month, but just the value. How do I do this?
So at that point, that sounds like a measures total problem. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
Sorry, @carolinastalo
copy-paste 🙂
Measure =
var _slicerDate = if(ISFILTERED(CalendarTable[Date]),CALCULATE(MAX(CalendarTable[Date])),today())
var _lastDate = CALCULATE(MAX('Table'[Date]),ALLEXCEPT('Table','Table'[Account]),'Table'[Date]<=_slicerDate)
return
CALCULATE(MAX('Table'[Balance End of month]),'Table'[Date]=_lastDate)
So like the attached?
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
16 | |
13 | |
10 | |
8 |
User | Count |
---|---|
59 | |
20 | |
12 | |
11 | |
10 |