Two types of fact tables in the same visualization

[zivhimmel]

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 !

[v-haibl-msft]

@zivhimmel

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

[zivhimmel]

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.

[v-haibl-msft · Accepted solution]

@zivhimmel

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

[zivhimmel]

Yes, I think this is it. Thanks !

[zivhimmel]

Ahm...anyone ?