Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
JR_2020
New Member

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

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

 

JR_2020_0-1679908698726.png

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.