Custom Month to date
Hello all, Our fiscal months are not based on calendar months but ends on the Saturday of every month. Therefore the our fiscal month starts on different day every month. I have the dax formula to calculate the last saturday of the month but I am not sure how to implement it to use in my slicer Here's the measure that give me the last saturday of the month customEOM = VAR dateVal = [Date] RETURN if ( dateVal > DATE ( '2018, 12, 31'), if ( dateVal > if ( WEEKDAY ( EOMONTH ( dateVal, 0 ), 1 ) = 7, EOMONTH ( dateVal, 0 ), EOMONTH ( dateVal, 0 ) - WEEKDAY ( EOMONTH ( dateVal, 0 ), 1 ) ), if ( WEEKDAY ( EOMONTH ( EOMONTH ( dateVal, 0 ), 1 ), 1 ) = 7, EOMONTH ( EOMONTH ( dateVal, 0 ), 1 ), EOMONTH ( EOMONTH ( dateVal, 0 ), 1 ) - WEEKDAY ( EOMONTH ( EOMONTH ( dateVal, 0 ), 1 ), 1 ) ), if ( WEEKDAY ( EOMONTH ( dateVal, 0 ), 1 ) = 7, EOMONTH ( dateVal, 0 ), EOMONTH ( dateVal, 0 ) - WEEKDAY ( EOMONTH ( dateVal, 0 ), 1 ) ) ), EOMONTH ( dateVal, 0 ) )
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
