Calculate last 3 months average Gross margin from selected date and 2 fact tables

[JR_2020]

Context: This is a farming business. Once we end harvesting the crop, we close the rotation. I would like to calculate the average gross margin of the harvest prior to the closing date of the rotation.

We have 5 tables, of which 2 fact tables:

- Fact Table PROD_Transaction - Contains transactions that make up the Cost of Goods Sold is calculated (transactions are use of inputs and labor)

- Fact Table Sales_Sales with sales transactions

-  DimTable PROD_Crops - Linked to both Production and Sales (akin to a product table)

- DimTable PROD_Rotations - Contains the closing date of the rotation, linked to the Production Fact Table

- DimTable date - with an active relation to both fact tables, and an inactive relation to the Rotation table

I would like to calculate the average gross margin in the last 3 months per product based on the close date of the rotation (e.g. If the rotation closed 1 April 2023 I would like to calculate the average gross margin between 1 Jan and 31 March 2023)

Any advice highly appreciated, thanks in advance

[amitchandak · Accepted solution]

@JR_2020 , You need to try measure like

3 Month Avg = CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[Value)))
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-3,MONTH))

You can also try window function

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc