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,
I'm trying to solve the following modeling challange. I have one solution in mind, which is a bit ugly,
and would be happy to hear other ideas.
The model is for a finanicial instituation. Let's simplify and say it's a bank.
We need to show in one visualization the net money transactions(+11M$ for example), as well as balance at the end of the period(350M$), grouped by days or months.
The money transactions are basically deposits and withdrawlas. If someone deposits 400$ on a specific month and withdraws 100$, that person contributes 300$ to the net money transaction of that month.
The balance, however, cannot be calculated using the transactions because it includes some movement that is not reresented in the transactions table(e.g. interest).
Data sources(very simplified) :
1. daily transaction aggregation fact : fact_transaction : user_id, date_id, net_amount
2. daily balance table : fact_balance : user_id, date_id,balance
3. dim_user and dim_date
The visualization should show something like that :
Month, net money growth, balance
(month can be replaced by year, day, week, quarter etc.).
The two fact tables are very different. fact_transaction is a transaction fact(can be aggregated) while fact_balance is a periodic snapshot and cannot be aggregated.
As I mentioned, I have a solution in mind, but it isn't pretty and I'd like to hear your ideas on how to enable this.
Solutions can be within Power BI desktop or on the database level(I'm the DBA too and can prepare the data in whatever way necessary).
Thanks !
Solved! Go to Solution.
Not sure if I understand your requirement correctly. If you want the monthly balance to be the balance value of last day of one month. You can try with following measure formula.
MonthBalance = CALCULATE ( SUM ( TestFactBalance[Balance] ), FILTER ( TestFactBalance, TestFactBalance[DateID] = MAX ( TestFactBalance[DateID] ) ) )
Best Regards,
Herbert
I think I have a general understanding to your requirement. But could you please give us some sample data and relationship of your tables? I also would like to see the expected output result.
Best Regards,
Herbert
Thanks ! Here's what you've asked for.
First, the relationship :
sample data(this is just a sample so doesn't necessarily make sense) :
And the result. There are two visualizations. The daily is fine - there's no problem with summarizing the daily balance.
The monthly is wrong because it takes all the daily balances and summarizes them.
I think I've made my peace with the following solution :
Keep a daily balance table like I have here + add another monthly balance table which will have only one balance record per user per month(last day of month or first day of month record).
The daily visualization will use the daily table, the monthly will use the monthly and that would be ok for this report.
It won't, however, allow for different date periods. For example, If I want to get info about the entire period of
August 3rd until August 15th(not daily breakdown), I can get the transaction data but not the balance.
I think my users can live with that and can go around it by just having different reports for balance and for transactions,
but still wanted to ask if someone has a better solution.
Thanks allot for your time.
Not sure if I understand your requirement correctly. If you want the monthly balance to be the balance value of last day of one month. You can try with following measure formula.
MonthBalance = CALCULATE ( SUM ( TestFactBalance[Balance] ), FILTER ( TestFactBalance, TestFactBalance[DateID] = MAX ( TestFactBalance[DateID] ) ) )
Best Regards,
Herbert
Yes, I think this is it. Thanks !
Ahm...anyone ?
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |