Bank Accounts that Change from Department to Department in a Daily Cash Report

[Tuta]

Setup:

I am trying to set up a very simple end of day cash balance report. Fact table will be a list of all bank accounts and the end of day cash balance. I have lookup tables keyed on account number to link things like Bank Name, Country, and Department.

Problem:

Over the course of the year, any single account may be moved from department to department. So, account 1111 might be in Department A for 1/1/2020 - 5/15/2020, and then moved to Department B for 5/16/2020-onward.

For my visuals, the user will be selecting a single date from a SLICER .

I want to make sure that when a date is picked, the Account's cash balance is included in the correct Department, but I am having trouble with writing that measure (or calculated column if that is best).

What is best practice for setting something like this up with effective dates? Thanks so much!

Example of Lookup table:

Test for May.xlsx
	A	B	C	D	E	F
1	Account Number	Country	Bank Name	Department	Department Start Date	Department End Date
2	1111	USA	Chase Bank	Department A	1/1/2020	6/30/2020
3	2222	USA	Bank of America	Department A	1/1/2020
4	3333	Australia	Australia National Bank	Department A	1/1/2020
5	4444	Brazil	Brazil Bank	Department A	1/1/2020
6	5555	Australia	Australia National Bank	Department B	1/1/2020
7	6666	Canada	Canada First Bank	Department B	1/1/2020
8	7777	Canada	Canada First Bank	Department B	1/1/2020
9	8888	UK	Royal Bank	Department C	1/1/2020
10	9999	USA	JP Morgan	Department C	1/1/2020
11	1111	USA	Chase Bank	Department B	7/1/2020
Sheet1

Fact table:

Test for May.xlsx
	A	B	C
1	Account Number	Cash Balance	Date
2	1111	$ 49,433,729.00	1/1/2020
3	2222	$ 43,764,083.00	1/1/2020
4	3333	$ 25,655,834.00	1/1/2020
5	4444	$ 3,078,702.00	1/1/2020
6	5555	$ 9,520,004.00	1/1/2020
7	6666	$ 15,658,861.00	1/1/2020
8	7777	$ 31,283,507.00	1/1/2020
9	8888	$ 48,068,531.00	1/1/2020
10	9999	$ 36,436,769.00	1/1/2020
11	1111	$ 38,056,864.00	1/2/2020
12	2222	$ 1,229,319.00	1/2/2020
13	3333	$ 6,587,544.00	1/2/2020
14	4444	$ 41,818,337.00	1/2/2020
15	5555	$ 9,010,454.00	1/2/2020
16	6666	$ 42,345,150.00	1/2/2020
17	7777	$ 23,146,931.00	1/2/2020
18	8888	$ 47,767,511.00	1/2/2020
19	9999	$ 9,722,558.00	1/2/2020
20	1111	$ 17,657,590.00	1/3/2020
21	2222	$ 50,448,822.00	1/3/2020
22	{ and so on and so on}
Sheet2

Cell Formulas
Range	Formula
B2:B21	B2	=RANDBETWEEN(0,55000000)

Example of visuals....

 

[alz]

1My Excel Trick Collection.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Account	Country	Bank Name	Department	Start Date	End Date
2	1111	USA	Chase Bank	Department A	01/01/2020	02/01/2020		Sorce Data for pivot
3	2222	USA	Bank of America	Department B	01/01/2020	02/01/2020		Account	Cash Balance	Date	Department	Country
4	3333	Australia	Australia National Bank	Department B	01/01/2020	05/01/2020		1111	39368758	01/01/2020	Department A	USA
5	4444	Brazil	Brazil Bank	Department B	01/01/2020	01/01/2020		2222	45523261	01/01/2020	Department B	USA
6	5555	Australia	Australia National Bank	Department C	01/01/2020	01/01/2020		3333	41929575	01/01/2020	Department B	Australia
7	1111	USA	Chase Bank	Department B	03/01/2020	04/01/2020		4444	858747	01/01/2020	Department B	Brazil
8	2222	USA	Bank of America	Department A	03/01/2020	04/01/2020		5555	47102655	01/01/2020	Department C	Australia
9	3333	Australia	Australia National Bank	Department B	06/01/2020	10/01/2020		1111	25660230	02/01/2020	Department A	USA
10	4444	Brazil	Brazil Bank	Department C	02/01/2020	04/01/2020		2222	17785418	02/01/2020	Department B	USA
11	5555	Australia	Australia National Bank	Department B	02/01/2020	02/01/2020		3333	24261234	02/01/2020	Department B	Australia
12	1111	USA	Chase Bank	Department A	05/01/2020	30/01/2020		4444	35967971	02/01/2020	Department C	Brazil
13	2222	USA	Bank of America	Department C	05/01/2020	28/01/2020		5555	24473488	02/01/2020	Department B	Australia
14	3333	Australia	Australia National Bank	Department B	11/01/2020	30/01/2020		1111	10664458	03/01/2020	Department B	USA
15	4444	Brazil	Brazil Bank	Department B	05/01/2020	30/01/2020		2222	38517779	03/01/2020	Department A	USA
16	5555	Australia	Australia National Bank	Department B	03/01/2020	30/01/2020		3333	17677407	03/01/2020	Department B	Australia
17								4444	48266977	03/01/2020	Department C	Brazil
18								5555	49205354	03/01/2020	Department B	Australia
19								1111	34045123	04/01/2020	Department B	USA
20								2222	5805862	04/01/2020	Department A	USA
21	Row Labels	Sum of Cash Balance			Row Labels	Sum of Cash Balance		3333	22362647	04/01/2020	Department B	Australia
22	Australia	$231,582,521.00			Department A	$109,352,629.00		4444	35112712	04/01/2020	Department C	Brazil
23	Brazil	$120,206,407.00			Department B	$293,356,873.00		5555	4570161	04/01/2020	Department B	Australia
24	USA	$217,370,889.00			Department C	$166,450,315.00
25	Grand Total	$569,159,817.00			Grand Total	$569,159,817.00
26
27
28
29
Sheet2

Cell Formulas
Range	Formula
K4:K23	K4	=INDEX($D$2:$D$16,SUMPRODUCT(IF(((H4=$A$2:$A$16)*(J4>=$E$2:$E$16)*(J4<=$F$2:$F$16))=1,ROW($D$2:$D$16)-ROW($D$1))))
L4:L23	L4	=INDEX($B$2:$B$16,MATCH(H4,$A$2:$A$16,0))
Press CTRL+SHIFT+ENTER to enter array formulas.

I think you can create helper column and pull the active department name from the bank and use cash balance table for pivot table or create a powery query.

 

[Tuta]

@alz - this does work and gets us to the desired result.

Is there an equivalent bit of DAX or M that we could replicate the INDEX/SUMPRODUCT work in excel to occur during load or once in Power BI?

Thanks so much!