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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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