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 all,
I want to calculate the average for the last 3 month and calculate the following formula:
PB is getting me this result for the 1st set of numbers: 3,268,796.13
But I'm expecting 3,268,821.13 =(3,265,269.82+3,269,615.35+3,271,578.22)/3
Solved! Go to Solution.
Hi @TheBaz_57 ,
In order to give you a suitable solution as soon as possible, could you please share some sample data and field settings for your matrix visual? If any measure or calculated column are referenced, please provide their formulas along with them.
Alternatively, you can refer to the following link to try to solve your problem.
Rolling 12 Months Average in DAX
CALCULATIONITEM 'PriceToUse'[Moving Average]."Rolling Avg 3M" =
VAR NumOfMonths = 3
VAR LastCurrentDate =
MAX ( 'Date'[Date] )
VAR Period =
DATESINPERIOD ( 'Date'[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR Result =
CALCULATE (
AVERAGEX (
VALUES ( 'Date'[Calendar Year Month] ),
SELECTEDMEASURE ()
),
Period
)
VAR FirstDateInPeriod = MINX ( Period, 'Date'[Date] )
VAR LastDateWithSales = MAX ( Sales[Order Date] )
RETURN
IF ( FirstDateInPeriod <= LastDateWithSales, Result )
Incorrect total values fix methods:
Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand
Dax for Power BI: Fixing Incorrect Measure Totals
Best Regards
Hi @TheBaz_57 ,
In order to give you a suitable solution as soon as possible, could you please share some sample data and field settings for your matrix visual? If any measure or calculated column are referenced, please provide their formulas along with them.
Alternatively, you can refer to the following link to try to solve your problem.
Rolling 12 Months Average in DAX
CALCULATIONITEM 'PriceToUse'[Moving Average]."Rolling Avg 3M" =
VAR NumOfMonths = 3
VAR LastCurrentDate =
MAX ( 'Date'[Date] )
VAR Period =
DATESINPERIOD ( 'Date'[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR Result =
CALCULATE (
AVERAGEX (
VALUES ( 'Date'[Calendar Year Month] ),
SELECTEDMEASURE ()
),
Period
)
VAR FirstDateInPeriod = MINX ( Period, 'Date'[Date] )
VAR LastDateWithSales = MAX ( Sales[Order Date] )
RETURN
IF ( FirstDateInPeriod <= LastDateWithSales, Result )
Incorrect total values fix methods:
Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand
Dax for Power BI: Fixing Incorrect Measure Totals
Best Regards
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |