The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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
Solved! Go to 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
@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
User | Count |
---|---|
16 | |
8 | |
7 | |
6 | |
6 |
User | Count |
---|---|
26 | |
13 | |
12 | |
8 | |
8 |