cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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])
``````Next_3_Months =
CALCULATE(SUM('Table'[Sales]),
FILTER(ALL('Table'),[Store_ID]=MAX('Table'[Store_ID])
``````
``````L3M_Avg =
var _count=
COUNTROWS(
SUMMARIZE(FILTER('Table',[Store_ID]=MAX('Table'[Store_ID])
return [Last_3_months]/_count``````
``````N3M_Avg =
var _count=
COUNTROWS(
SUMMARIZE(FILTER('Table',[Store_ID]=MAX('Table'[Store_ID])
[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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.