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.
Dear All,
I have this simple scenario where i need to calculate a rolling past 6 months Average for a given data set, below details.
Data Set Example:
The Year_Month column, in data source only had year and month, and i converted it to Data Type Date/Time n Power BI, hence shows Date as 1st date of that month.
I don't want to go the date level in the data source as it will be millions of rows of data.
Calendar Year_Month | Month | Query | Distinct User |
1/12/2019 | 12 | DSA | 10 |
1/01/2020 | 1 | DSA | 1 |
1/02/2020 | 2 | DSA | 20 |
1/03/2020 | 3 | DSA | 10 |
1/04/2020 | 4 | DSA | 30 |
1/05/2020 | 5 | DSA | 40 |
1/12/2019 | 12 | Sales | 23 |
1/01/2020 | 1 | Sales | 21 |
1/02/2020 | 2 | Sales | 20 |
1/03/2020 | 3 | Sales | 9 |
1/04/2020 | 4 | Sales | 8 |
1/05/2020 | 5 | Sales | 10 |
1/12/2019 | 12 | Stock | 33 |
1/01/2020 | 1 | Stock | 41 |
1/02/2020 | 2 | Stock | 39 |
1/03/2020 | 3 | Stock | 21 |
1/04/2020 | 4 | Stock | 20 |
1/05/2020 | 5 | Stock | 11 |
DAX Formula used:
I have created a Date table in Power BI and referring that in the DATESINPERIOD function below.
DIVIDE(CALCULATE (
SUM(Rolling_Average_Example[Distinct User]),
DATESINPERIOD ( 'Date Table'[Date], MAX(Rolling_Average_Example[Calendar Year_Month]) , -6, month )
),6,0)
Expected Result:
For month of May, the rolling 6 month Average should be Sum of users in past 6 months/6 = 306/6 = 51.
But the DAX above shows 10, which is SUM of users in MAY divided by 6 (61/6=10), which is wrong.
If you can guide me where i am wrong and how to fix this in given scenario.
Solved! Go to Solution.
Hi,
Please create a What If parameter first.
Then try this measure:
Measure =
AVERAGEX (
DISTINCT ( 'Table'[Calendar Year_Month] ),
CALCULATE (
IF (
YEAR ( MAX ( 'Table'[Calendar Year_Month] ) ) = YEAR ( TODAY () )
&& MONTH ( MAX ( 'Table'[Calendar Year_Month] ) ) = MONTH ( TODAY () ),
0,
IF (
MONTH ( TODAY () ) - Previous[Previous Value] <= 0,
CALCULATE (
SUM ( 'Table'[Distinct User] ),
FILTER (
'Table',
'Table'[Calendar Year_Month] < DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
&& 'Table'[Calendar Year_Month]
>= DATE ( YEAR ( TODAY () ) - 1, 12
- ABS ( MONTH ( TODAY () ) - Previous[Previous Value] ), 1 )
)
),
CALCULATE (
SUM ( 'Table'[Distinct User] ),
FILTER (
'Table',
'Table'[Calendar Year_Month] < DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
&& 'Table'[Calendar Year_Month]
>= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - Previous[Previous Value], 1 )
)
)
)
)
)
)
When select one value in slicer, the result shows:
See my attached pbix file.
Best Regards,
Giotto
Hi,
Please create a What If parameter first.
Then try this measure:
Measure =
AVERAGEX (
DISTINCT ( 'Table'[Calendar Year_Month] ),
CALCULATE (
IF (
YEAR ( MAX ( 'Table'[Calendar Year_Month] ) ) = YEAR ( TODAY () )
&& MONTH ( MAX ( 'Table'[Calendar Year_Month] ) ) = MONTH ( TODAY () ),
0,
IF (
MONTH ( TODAY () ) - Previous[Previous Value] <= 0,
CALCULATE (
SUM ( 'Table'[Distinct User] ),
FILTER (
'Table',
'Table'[Calendar Year_Month] < DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
&& 'Table'[Calendar Year_Month]
>= DATE ( YEAR ( TODAY () ) - 1, 12
- ABS ( MONTH ( TODAY () ) - Previous[Previous Value] ), 1 )
)
),
CALCULATE (
SUM ( 'Table'[Distinct User] ),
FILTER (
'Table',
'Table'[Calendar Year_Month] < DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
&& 'Table'[Calendar Year_Month]
>= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - Previous[Previous Value], 1 )
)
)
)
)
)
)
When select one value in slicer, the result shows:
See my attached pbix file.
Best Regards,
Giotto
@Anonymous , please find the attached pbix after the signature. I am getting 50.
@amitchandak Thanks for your response, the sample data i posted is in format dd/mm/yyyy, so it is actually user counts for different months of the year 2019 and 2020, sorry i should have been clear with format.
Attached screenshot comparing how the data looks on my side and what i could see in the pbix file shared by you (left hand side is data on my side)
@Anonymous , now I am getting 60. What should not be there in 6 months?
File attached after signature
@amitchandak , Based on the dataset i shared, i want to calculate the rolling 6 months average, i took May as example, in initial post what is the expected value of rolling 6 months average as compared to what is happening now.
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 |
---|---|
114 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |