Need help with DAX to create report

[Anonymous]

Hi!

I have a particular business case to create this report (the 5 left columns below). First a short explaination. The first column is a date column, and the next 4 columns are something we call a fishgroup. A fishgroup is time range before a specific action is taken, which can be move fish to another fishgroup or deliver fish. Fishgroup 1 has a time range from 2 jan 2019 including 5 jan 2019 while Fishgroup 3 has range from 3 jan including 5 jan 2019. On day shift between 5 jan and 6 jan, 60% of the fish in FG1 is moved to FG2 and 40% to FG4. Likewise 100% of FG3 is moved to FG4. FG1 and FG3 ends, and now FG2 and FG4 are active. FG2 ends at 8 jan and FG4 ends 11 jan when the fish is sold (delivered). Between every day shift a small number of fish dies which explains why the number is getting smaller each day.

So to the report. I want to see lifetime for the count of fish for the active fishgroups on a particular day. Lets say I want to look at 11. january, only FG4 is active. In the report we will only see the FG4 column and date column. And from 5 jan to 2 jan FG4 is virtually getting fish from FG1 and FG3 (showed in red). The actual computing you will see rightmost column which is not part of the report, just for explaination. I need to create some measures to make this report possible.

Measure 1 (Get all Fish groups (delivery) with Fish within a date filter) = ... on 11.1 only FG4 is returned

Measure 2 (Calculate number of fish) = for each day status multiply it with the factor * source Fishgroup

For 6 to 11. january it is multiplied with 1 but from 2 to 5 january, FG4 has fish from FG1 with factor 0.4 and from FG3 with factor 1.

My calculations - the first should show trace like in the report

(End) Number of Fish UB =

VAR dato =

MAX ( 'Date'[Date Key] )

RETURN

SUMX (

VALUES ( 'BioMass'[Fishgroup Key] ),

CALCULATE (

CALCULATE (

[(End) Number of Fish UB Source FG],

FILTER (

VALUES ( 'Fishgroup'[FishGroup End Date Key] ),

'Fishgroup'[FishGroup End Date Key]

= MAX ( 'Fishgroup'[FishGroup End Date Key])

)

)

,

'Fishgroup'[FishGroup Start Date Key] <= dato ,

'Date'[Is Current Day] = "Earlier" )

)

(End) Number of Fish UB Source FG = SUMX (

VALUES('TraceBack'[Source FishGroup Key]),

CALCULATE (

[Number Of Fish UB Base]

* [Qty Factor Base],

LASTDATE('Date'[Date]) )

)

Number Of Fish UB Base =

VAR IB_Antall =

CALCULATE (

SUM ( BioMass[NumberOfFish]),

FILTER (

BioMass,

BioMass[Date Key]

= CALCULATE (

LASTNONBLANK ( BioMass[Date Key], 1 ),

FILTER ( BioMass, BioMass[StatusDate]<= LASTDATE ( 'Date'[Date] ) )

)

)

)

RETURN

IB_Antall

So far I have though of a data model like below. The traceback table is a bridge table simulating a M to M relationship. Each destination fishgroup has a relation to source fishgroup and a factor

FISH FACT

Date Key Cage Key Fishgroup Key StatusTime NumberOfFish StatusType
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
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
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
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

FISH GROUP TABLE

FishGroup Key Cage Key FishgroupName FishGroup Start Date Key FishGroup End Date Key IsDelivery
1 1 B-001.000001 20190102 20190106 0
2 1 B-001.000002 20190105 20190109 1
3 2 B-001.000003 20190103 20190106 0
4 2 B-001.000004 20190105 20190112 1

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 · Accepted solution]

First, I really appreciate this post, this is a model post. And, because of that, I spent some extra time on this because this was not easy to puzzle out. But, I believe I got it.

FG = 
VAR __all = ALL(fishfacts)
VAR __date = MAX([Date])
VAR __FG = SWITCH(MAX(FGs[FG]),"FG1",1,"FG2",2,"FG3",3,"FG4",4)
VAR __isDelivery = MAXX(FILTER(fishgroups,[FishGroupKey] = __FG),[IsDelivery])
VAR __table = SUMMARIZE(FILTER(__all,[FishgroupKey]<=__FG),[Date],[FishgroupKey],"__fg",__FG,"__fish",SUM(fishfacts[NumberOfFish]))
VAR __table1 = ADDCOLUMNS(__table,"__factor",LOOKUPVALUE(fishfactors[QtyFactor],fishfactors[SourceFishGroupKey],[FishgroupKey],fishfactors[DestinationFishGroupKey],[__fg]))
VAR __table2 = ADDCOLUMNS(__table1,"__final",[__fish]*[__factor])
RETURN
    IF(
        __isDelivery = 0,
        SUMX(FILTER(__table,[FishgroupKey] = __FG && [Date] = __date),[__fish]),
        SUMX(FILTER(__table2,[Date] = __date),[__final])
    )

Attached PBIX. 

[Anonymous]

Hello again. Your solution is excellent, and it works. But I was a little unprepared for how the solution would work. So, here is the additional requirements:

[Anonymous]

Hello again. Your solution is excellent, and it works. But I was a little unprepared for how the solution would work. So, here is the additional requirements:

First, a general requirement is:
- Show only fish groups that have fish
- If the report is not broken down by fish groups, it should sum all actual fish groups that have fish
NB! There could be many more fish group than just 4, so the DAX calculation needs to be aware of that
So, if current day is 2. Jan or this day is selected on a date filter, only FG1 has fish, one level up or Total is 990

Here you see that only FG2 and FG4 has data but will also have trace back. If we remove fis group from the report we will end up with sum of acutal fish.

Hopefully you understand how it works. So, if you have some recommendations on your dax code it would be super.

Again, thank you very much fo spending time on me

Regards Geir

[Greg_Deckler]

I'm afraid I do not understand everything going on here. Perhaps if you could break it down into a set of business rules like:

- Non-delivery fish groups have fish but should only be displayed up until a delivery date

- Delivery fish groups only get fish once their delivery date becomes active

Something that breaks down the logic that you are trying to convey because it's too confusing the way you are explaining it right now.