average last 3 month

3 Topics

Calculate 3 Months Moving Average
I would like to calculat 3 months moving average, The month in my Power Pivot are across and not down. In the data model I have created a date table. Any suggestions would be great. UNFORTUNATLY I can't upload/downlad anything from my work PC.
meierli
2 years ago Place DAX Commands and Tips
2.2KViews
0likes
5Comments
Moving annual average by 3 months
Hi there, I have the following date table by year and month. It includes dates from 2020 and is continually updated. I am trying to create an additional column called MAA that calculates the rolling average of the 'Monthly Total' every 3 months, but failing terribly. Any ideas? Any help greatly appreciated Year Month Monthly Total 2020 February 56 2020 April 15 2021 March 49 2021 July 72 2021 April 59 2021 December 31 2021 November 97 2021 January 57 2021 October 51 2021 September 64 2021 May 78 2021 June 64 2021 August 24 2021 February 33 2022 September 63 2022 January 33 2022 April 40
Solved
ClemFandango
3 years ago Place DAX Commands and Tips
791Views
0likes
2Comments
Calculate Average of Last 3 months with Fiscal month = calendar month
Hi Everyone, I have a Sales dashboard with requirement to calculate average of last 3 months(including current month). The data does not have a date column, however has fiscal year, fiscal quarter, fiscal month & fiscal week columns. I have created a custom date column as below : FY Date = DATE( 'SalesGenie VN'[Fiscal Year], 'SalesGenie VN'[Fiscal Month] ,1 ) which gives date in below format : 01/MM/YYYY Fiscal month has values as below . i.e) Jan =1 , Feb = 2 ...... Nov = 11, Dec = 12 . However Fiscal year is between May to April, which means that May 2023 (fiscal month = 5 ) is start of fiscal year and April 2024 ( fiscal month = 4 ) is end of fiscal year. The client has data structured in this way , so can't change it. I have a DAX created as below which works fine for a couple of months . Average Sales Out Last 3 Months = VAR v2 = CALCULATE ( AVERAGEX ( VALUES ( 'SalesGenie VN'[Fiscal Month] ), CALCULATE ( SUM ( 'SalesGenie VN'[Actual Sales in DMS] ), 'SalesGenie VN'[Data Source] = 1 ) ), DATESINPERIOD ( 'SalesGenie VN'[FY Date], MAX ( 'SalesGenie VN'[FY Date] ), -3, MONTH ), ALL ('SalesGenie VN'[Fiscal Year],'SalesGenie VN'[Fiscal Quarter], 'SalesGenie VN'[Fiscal Month], 'SalesGenie VN'[Month Name]),ALL('Month Ranking'[Month]) ) RETURN v2 The problem I am facing is that , If Jan is selected , by default Average is calculated as : Average ( Jan 2023, Dec 2022, Nov 2022) . Similarly for Feb , Average (Feb 2023, Jan 2023 , Dec 2022). My requirement is that if Jan is selected, Average should be calculated as : Average ( Jan 2023, Dec 2023 , Nov 2023) if Feb is selected, Average (Feb 2023, Jan 2023, Dec 2023) if May is selected, Average ( May 2023, Apr 2022, Mar 2022) ........ taking into consideration Fiscal Yr if Jun is selected, Average ( Jun 2023, May 2023, Apr 2022) Could you please help in modifying the DAX which I have already written, to meet this requirement .. Or a new idea would also work. Thanking you in advance .. Swathi amitchandak tamerj1 FreemanZ johnt75
Anonymous
3 years ago Place DAX Commands and Tips
1.2KViews
0likes
3Comments