Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
You must add a calendar table to your data model. Ideally use an externally maintained table that conforms to your client's fiscal calendar. Don't try to recreate that in Power Query or DAX.
Hi @lbendlin ,
We don't have a date column at all in the data . ( Only till week level we have data)
You must add a day column, even if you have to derive it from the first day of each week.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
51 | |
27 | |
24 | |
13 | |
9 |
User | Count |
---|---|
74 | |
56 | |
47 | |
16 | |
12 |