Have a complex DAX challenge and need some advise how to solve it

[Anonymous]

I 

have challenge in Power BI constructing what you see in the picture below. Briefly explained, I need to be able to trace the fish. The fish stocking will be put into cages but here we will also put them into what we call fishgroups. A fishgroup is just a defined period of time the fish stay in the group (start,end). For each day we count how many fish is still alive. After a while the fish is growing and must be sorted and new fish groups are created. In the picture there are 4 fishgroups where fish first is put into FG1 and FG3. After 5 days, FG1 is sorted and 60% is sent to FG2 and 40% sent to FG4. In FG3 is all fish moved to FG4. FG1 and FG3 ends here. FG2 ends when fish are sold. The same happends later for FG4. This is only a brief example. There can be many more sorting. Performance can be an issue. My first thoughts were to create data model with a fact table called BiomassView. It has a relationship M:1 to FishGroupView. We need a way to calculate the weighted numbers, so I created a bridge table to manage that a fishgroup is related to other fishgroups.

What I need help to is to construct a DAX Measure that when summarize on each day you get what you see in the End of Day column. When adding Fishgroups to the visualization, you will see how fish are related to fishgroups in separate columns but still the daily sum should be correct. As you seem the spilt operation is happen later on the day, but latest timestamp per day per fishgroup is thing here. In row 8 FG1 shows but it is splitted into FG2 and FG4, It still should be displayed. FG2 should display FG1*0,6, while FG4 should show FG1*0,4 + FG3*1 and the end sum for the day should be FG1*0,6,+ FG1*0,4 + FG3*1 (or FG2+FG4 if stored)

Below you see my data model using a brigde table to handle many-may relationhip between the fishgroups. I also have the Power BI file if someone is interested to look at it. Perhaps that will be more easy. You can download it from here https://1drv.ms/u/s!AnHMZqeRJMuloW_-10_HFKatGPwu?e=FNK48d

Hopefully someone can give me some hints here. Perhaps I can do something with my data model that will ease the problem. I am open for everything. Once again thank you for spending your time

Regards Geir 

[Anonymous]

Hi again and tknaks for the answers. Perhaps I need to explain how this should work. Below I have written out the content of the tree important tables (small amount of data of course)

Let us start what is happen. When the fish is put out it will create the first Fish Group row in the FishGroup dimension, the start date will be known, it will create a row in the fact table saying 1000 fish are in stock. It will also create a row in the bridge table saying the qty factor is 1 where destination FG and source FG are the same. The next days the number of fish in stock changes giving new status in the fact table but the calculation has the same factor.

The 2.1.2019 we are setting out 1000 fish into FG3, a new row is inserted to fishgroup table, likewise to a new row into the fact table and also the bridge table with qty factor 1 and dest and source fishgroup are the same.

Everything goes as normal until we are coming to 5.1.2019 (dd.mm.yyyy), things change. Two new fishgroups are created FG2 and FG4. We are splitting out fishgroup 1 sending 60% of the content to FG2 and 40% to FG4. Likewise is 100% of FG3 sent to FG4. The interesting thing here is what is happening in the bridge table. Destination FG 2 has 60% of content from FG1, but also 100% of its self. FG4 has 40%of the content of FG1 and has 100% of content of FG3 and 100% of itsself. In a report from 1.1.2019 to 4.1.2019 only fishgroup 1 and 3 will be in it. if the report will span from 1.1.2019 to 11.01.2019 all fishgroups will be in it, but the measure must find the right number of fish. There must not be any double counting as can be in the 5.1.2019. And the last timestamp each day is the only valid.

I hope this scenario can help to understand the problem I have

Fact Table

Date Key	Cage Key	Fishgroup Key	StatusTime	NumberOfFish	StatusType
20190101	1	1	01/01/2019 12:00	1000	0
20190102	1	1	02/01/2019 00:00	990	1
20190103	1	1	03/01/2019 00:00	980	1
20190104	1	1	04/01/2019 00:00	970	1
20190105	1	1	05/01/2019 00:00	960	1
20190105	1	1	05/01/2019 12:00	955	2
20190105	1	2	05/01/2019 12:00	573	0
20190106	1	2	06/01/2019 00:00	568	1
20190107	1	2	07/01/2019 00:00	558	1
20190108	1	2	08/01/2019 00:00	548	1
20190108	1	2	08/01/2019 12:00	543	2
20190102	2	3	02/01/2019 12:00	1000	0
20190103	2	3	03/01/2019 00:00	985	1
20190104	2	3	04/01/2019 00:00	970	1
20190105	2	3	05/01/2019 00:00	955	1
20190105	2	3	05/01/2019 12:00	948	2
20190105	2	4	05/01/2019 12:00	1330	0
20190106	2	4	06/01/2019 00:00	1320	1
20190107	2	4	07/01/2019 00:00	1310	1
20190108	2	4	08/01/2019 00:00	1300	1
20190109	2	4	09/01/2019 00:00	1290	1
20190110	2	4	10/01/2019 00:00	1280	1
20190111	2	4	11/01/2019 00:00	1270	1
20190111	2	4	11/01/2019 12:00	1265	2

FishGroup Dimension Table

FishGroup Key	Cage Key	StartTime	EndTime	FishgroupName	FishGroup Start Date Key	FishGroup End Date Key	IsDelivery
1	1	01/01/2019 12:00	05/01/2019 12:00	B-001.000001	20190101	20190106	0
2	1	05/01/2019 12:00	08/01/2019 12:00	B-001.000002	20190105	20190109	1
3	2	02/01/2019 12:00	05/01/2019 12:00	B-001.000003	20190102	20190106	0
4	2	05/01/2019 12:00	11/01/2019 12:00	B-001.000004	20190105	20190112	1

TraceBack Bridge Table

Qty Factor	Destination FishGroup Key	Source FishGroup Key
1	1	1
0.6	2	1
1	2	2
1	3	3
0.4	4	1
1	4	3
1	4	4

[Greg_Deckler]

I may be mistaken but I think you want something like my time interval quick measures. Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365