Help Making a Calculated Table using DAX (Examples and Data provided)

[LadaA]

Hi everyone,

I'm new to Power BI and I'm trying to create a new table for this project within BI using DAX functions, any help would be greatly appreciated. I've had some success with using crossjoin to create the table template but I'm now stuck with the final calculation. 

All the excel tables have been pasted below if you'd like to take a look at it.

The TLDR is that there are farms that are running from "Start Date" to "End Date", each farm produces a fixed yearly yield of a specific product: Fruit, Vegies, Grain, Meat (Measured in Annual LB Produced). Each farm is individually instructed what % of their yearly LB produced is to be sent to any given state. How can I write a DAX function that will make a table that accumulates all of the produce sent to each state for any given year? Examples and a more in-depth breakdown is provided below.

Thank you all for your time, if you have any questions please leave a comment and I'll answer it asap.

=====================================================================

The premise of this project is that there is a large grocery store company that owns multiple farming plants that each produce a fix yearly yield of a specific product, measured in LB of Produce. The company has a goal to supply x states with y LB of products by year 2030.

Example Inputs

Main Parameters:

Grocery Store Company
Start Year	2019
End Year	2030

Total Produce Needed by State:

State	LB of Produce Needed
AR	167,755.15
CA	623,050.39
MI	312,832.94
NH	44,286.41
OH	672,908.93
TX	656,893.41
VA	422,508.56

The company notates how much of the plants yearly produces goes to a given state by assigning the states column the %. Meaning that if they would like Plant 1 to send 20% of its 47563 Annual LB Produced to California they'd mark CA with 0.20. 

Which would mean that in the year 2019 CA receives 9251.98 Annual LB of Fruit since the plant starts on the 11th of January but in the following years until the End Date year CA would be receiving 9512 Annual LB of Fruit from Plant 1.

Farming Projects:

Plant	Plant Name	Product Type	Start Date	End Date	Annual LB Produced	AL	AK	AZ	AR	CA	CO	CT	DE	FL	GA	HI	ID	IL	IN	IA	KS	KY	LA	ME	MD	MA	MI	MN	MS	MO	MT	NE	NV	NH	NJ	NM	NY	NC	ND	OH	OK	OR	PA	RI	SC	SD	TN	TX	UT	VT	VA	WA	WV	WI	WY
1	A	Fruit	1/11/2019	11/1/2036	47563	0	0	0	0	0.2	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0.1	0	0	0	0	0	0	0.7	0	0	0	0	0	0	0
2	B	Meat	1/1/2019	6/1/2023	22128	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0
3	C	Fruit	11/1/2023	11/1/2035	97860	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
4	D	Vegies	1/1/2025	12/1/2034	24650	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
5	E	Grain	4/1/2024	1/1/2037	272804	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0
6	F	Meat	11/1/2022	6/1/2023	36542	0	0	0	0.1	0	0	0	0	0	0	0.3	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0.2	0	0	0	0	0	0	0.1	0	0	0	0	0	0	0.2	0	0	0.1	0	0	0	0

Example of Final Table needed for 2022:

*The Final Table should go from Start Year to End Year listed in the Main Parameters

*I'll try to provide a table for 2019-2022 in the comments as I've hit my character limit

*Other category = LB of Produce Needed - (Fruit + Vegies + Grain + Meat)

Year	State	Type	LB of Produce
2022	AR	Fruit	0.00
2022	AR	Vegies	0.00
2022	AR	Grain	0.00
2022	AR	Meat	610.70
2022	AR	Other	167144.45
2022	CA	Fruit	9512.60
2022	CA	Vegies	0.00
2022	CA	Grain	0.00
2022	CA	Meat	0.00
2022	CA	Other	613537.79
2022	MI	Fruit	0.00
2022	MI	Vegies	1826.10
2022	MI	Grain	0.00
2022	MI	Meat	0.00
2022	MI	Other	311006.84
2022	NH	Fruit	0.00
2022	NH	Vegies	0.00
2022	NH	Grain	0.00
2022	NH	Meat	1221.40
2022	NH	Other	43065.01
2022	OH	Fruit	4756.30
2022	OH	Vegies	0.00
2022	OH	Grain	0.00
2022	OH	Meat	610.70
2022	OH	Other	667541.93
2022	TX	Fruit	33294.10
2022	TX	Vegies	0.00
2022	TX	Grain	0.00
2022	TX	Meat	1221.40
2022	TX	Other	622377.91
2022	VA	Fruit	0.00
2022	VA	Vegies	0.00
2022	VA	Grain	0.00
2022	VA	Meat	22738.70
2022	VA	Other	399769.86

[barritown · Accepted solution]

Hi @LadaA,

Here's the file - https://www.dropbox.com/s/5ds3lq8u8m1qfcy/community-grocery.pbix?dl=0

I am not sure the calculations are 100% correct - up to you to check and validate.

Others calculated via your formula turn negative in 2023+ for some of the states, but that's correct in terms of figures.

Unfortunately, my solution requires a couple of permanent temporary tables. Hope that won't be a problem for you.

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[devanshi]

TotalProduceSent = CALCULATE( SUM(Farms[Annual LB Produced]),
                                     FILTER( Farms, Farms[Year] = EARLIER(CombinationTable[Year]) &&
                                                   Farms[Product] = EARLIER(CombinationTable[Product]) &&
                                                    RELATED(States[State]) = EARLIER(CombinationTable[State])
                                              )
)