Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
I'm struggling to find the correct answer. I have a sheet with bank balances aggregated. What i want to do is:
"For each bank, look me up the last day within the month, and save that balance". So far what i've tried is:
Solved! Go to Solution.
Hi @u92690
If you want a measure, you could try this one
Last Balance Total =
VAR _table = SUMMARIZE('Table','Table'[Bank],"Last_Date",MAX('Table'[Date]))
VAR _table2 = ADDCOLUMNS(_table,"Last Balance",SUMX(FILTER('Table','Table'[Bank]=[Bank]&&'Table'[Date]=[Last_Date]),'Table'[Balance]))
RETURN
SUMX(_table2,[Last Balance])
Add a calendar table to your model and create a relationship between it and balance table on date columns.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi @u92690
If you want a measure, you could try this one
Last Balance Total =
VAR _table = SUMMARIZE('Table','Table'[Bank],"Last_Date",MAX('Table'[Date]))
VAR _table2 = ADDCOLUMNS(_table,"Last Balance",SUMX(FILTER('Table','Table'[Bank]=[Bank]&&'Table'[Date]=[Last_Date]),'Table'[Balance]))
RETURN
SUMX(_table2,[Last Balance])
Add a calendar table to your model and create a relationship between it and balance table on date columns.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
I'm sorry but i don't think this solution fits my needs. I want to sum the last balance of the month for each bank, but i could have one bank whose last balance is the 01/25/2021 and another one is 01/31/2021. Therefore, I can't use the last date in the whole database for january, but the formula needs to catch the last balance for that bank no matter what the day is.
@u92690 , Based on what I got
closingbalancemonth(Sum('Table'[Value]), [balance_sum])
or
CALCULATE(Lastnonblankvalue('Test last saldo'[Date] , [balance_sum]) , filter(all('Date') , 'Date'[Month Year] = max('Date'[Month Year] )))
refer if needed
https://www.youtube.com/watch?v=yPQ9UV37LOU
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 |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |