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.

Power BI Monthly Update - August 2024

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

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Fabric Community Update - September 2024

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

Top Solution Authors
Top Kudoed Authors