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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
rookie_963
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_IDTransaction_DateUpgrade_dateSales
11AB01-01-202124-04-2021100
11AB12-01-202124-04-202180
11AB22-02-202124-04-2021120
11AB13-03-202124-04-2021200
11AB14-04-202124-04-2021250
11AB24-04-202124-04-2021110
11AB05-05-202124-04-2021290
11AB06-05-202124-04-202170
11AB07-05-202124-04-2021130
11AB08-06-202124-04-202150
24AC01-05-202206-09-2022140
24AC12-06-202206-09-2022180
24AC22-06-202206-09-2022160
24AC15-07-202206-09-20220
24AC13-08-202206-09-2022250
24AC14-08-202206-09-2022250
24AC24-08-202206-09-2022170
24AC05-09-202206-09-2022190
24AC06-09-202206-09-2022170
24AC07-09-202206-09-2022330
24AC08-10-202206-09-202260


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_IDUpgrade_dateSalesLast_3_months_(L3M)Next_3_Months_(N3M)L3M_AvgN3M_Avg
 11AB24-04-20211400500900167300
 24AC06-09-202219001150750383375
Total  330016501650275338


any help is appreciated, Thanks

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
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:

vyalanwumsft_0-1666258406974.png


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.

View solution in original post

3 REPLIES 3
v-yalanwu-msft
Community Support
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:

vyalanwumsft_0-1666258406974.png


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.

v-yalanwu-msft
Community Support
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:

vyalanwumsft_0-1665563956855.png


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.

Hi @v-yalanwu-msft 

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

 

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.