Skip to main content
cancel
Showing results for 
Search instead 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

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
Europe Fabric Conference

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.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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