cancel
Showing results for
Search instead for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

New Member

## Calculate Last 3 months Average when two date columns are present

Hi
Below is the Table structure I've got, where I have Store_ID, Transaction_date, Upgrade_date (store upgraded), Sales.

 Store_ID Transaction_Date Upgrade_date Sales 11AB 01-01-2021 24-04-2021 100 11AB 12-01-2021 24-04-2021 80 11AB 22-02-2021 24-04-2021 120 11AB 13-03-2021 24-04-2021 200 11AB 14-04-2021 24-04-2021 250 11AB 24-04-2021 24-04-2021 110 11AB 05-05-2021 24-04-2021 290 11AB 06-05-2021 24-04-2021 70 11AB 07-05-2021 24-04-2021 130 11AB 08-06-2021 24-04-2021 50 24AC 01-05-2022 06-09-2022 140 24AC 12-06-2022 06-09-2022 180 24AC 22-06-2022 06-09-2022 160 24AC 15-07-2022 06-09-2022 0 24AC 13-08-2022 06-09-2022 250 24AC 14-08-2022 06-09-2022 250 24AC 24-08-2022 06-09-2022 170 24AC 05-09-2022 06-09-2022 190 24AC 06-09-2022 06-09-2022 170 24AC 07-09-2022 06-09-2022 330 24AC 08-10-2022 06-09-2022 60

Below is the result that I'm trying to get,  My last 3 Months should be based on Upgade date, If I have an upgrdae date in April, I should consider my last 3 months from 1st Jan till 31st March (April excluded) and My next 3 months should be from 1st April until 30th June. My last and next 3 months average should be as shown in the below table, and my next 3 months (future months) should be based on number of months present in future, If I have data only till October and my upgrade_date is September, I should consider average sales for those 2 months i.e (Sep Sales + Oct Sales)/2. Finally Totals also should be average instead of sum for L3M Avg and N3M Avg

 Store_ID Upgrade_date Sales Last_3_months_(L3M) Next_3_Months_(N3M) L3M_Avg N3M_Avg 11AB 24-04-2021 1400 500 900 167 300 24AC 06-09-2022 1900 1150 750 383 375 Total 3300 1650 1650 275 338

any help is appreciated, Thanks

1 ACCEPTED SOLUTION
Community Support

Hi, @rookie_963 ;

You could create another measure.

``n3m_ave = AVERAGEX(SUMMARIZE('Table',[Store_ID],[Upgrade_date],"1",[N3M_Avg]),[1])``

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Community Support

Hi, @rookie_963 ;

You could create another measure.

``n3m_ave = AVERAGEX(SUMMARIZE('Table',[Store_ID],[Upgrade_date],"1",[N3M_Avg]),[1])``

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support

Hi, @rookie_963 ;

Try this measures:

``````Last_3_months =
CALCULATE(SUM('Table'[Sales]),FILTER(ALL('Table'),
[Store_ID]=MAX('Table'[Store_ID])
&&[Transaction_Date]<=EOMONTH(MAX('Table'[Upgrade_date]),-1)
&&[Transaction_Date]>EOMONTH(MAX('Table'[Upgrade_date]),-4)))``````
``````Next_3_Months =
CALCULATE(SUM('Table'[Sales]),
FILTER(ALL('Table'),[Store_ID]=MAX('Table'[Store_ID])
&&[Transaction_Date]>EOMONTH(MAX('Table'[Upgrade_date]),-1)
&&[Transaction_Date]<=EOMONTH(MAX('Table'[Upgrade_date]),2)))
``````
``````L3M_Avg =
var _count=
COUNTROWS(
SUMMARIZE(FILTER('Table',[Store_ID]=MAX('Table'[Store_ID])
&&[Transaction_Date]<=EOMONTH(MAX('Table'[Upgrade_date]),-1)
&&[Transaction_Date]>EOMONTH(MAX('Table'[Upgrade_date]),-4)),[Transaction_Date].[Year],[Transaction_Date].[Month]))
return [Last_3_months]/_count``````
``````N3M_Avg =
var _count=
COUNTROWS(
SUMMARIZE(FILTER('Table',[Store_ID]=MAX('Table'[Store_ID])
&&[Transaction_Date]>EOMONTH(MAX('Table'[Upgrade_date]),-1)
&&[Transaction_Date]<=EOMONTH(MAX('Table'[Upgrade_date]),2)),
[Transaction_Date].[Year],[Transaction_Date].[Month]))
return [Next_3_Months]/_count``````

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

New Member

Hi @v-yalanwu-msft

I really appreciate your help, for the above problem I want average to be computed in Totals

I currenlty see that totals are based on max date, if I'm not wrong.

## Helpful resources

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors