Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hi there,
I need to calculate average revenue based on last 12 month.
What would be the simple measure to calculate the same.
Cheers,
Solved! Go to Solution.
@chavanr so for months they also have a patten and this is the relevant calculation:
Sales AVG 1Y :=
VAR MonthsInRange = 12
VAR LastMonthRange =
MAX ( 'Date'[Year Month Number] )
VAR FirstMonthRange =
LastMonthRange - MonthsInRange + 1
VAR Period1Y =
FILTER (
ALL ( 'Date'[Year Month Number] ),
'Date'[Year Month Number] >= FirstMonthRange
&& 'Date'[Year Month Number] <= LastMonthRange
)
VAR Result =
IF (
COUNTROWS ( Period1Y ) >= MonthsInRange,
CALCULATE (
AVERAGEX ( Period1Y, [Sales Amount] ),
REMOVEFILTERS ( 'Date' )
)
)
RETURN
Result
https://www.daxpatterns.com/month-related-calculations/
@chavanr if it's average by day then this is an option:
Sales AVG 1Y :=
VAR Period1Y =
CALCULATETABLE (
DATESINPERIOD (
'Date'[Date],
MAX ( 'Date'[Date] ),
-1,
YEAR
),
'Date'[DateWithSales] = TRUE
)
VAR FirstDayWithData =
CALCULATE (
MIN ( Sales[Order Date] ),
REMOVEFILTERS ()
)
VAR FirstDayInPeriod =
MINX ( Period1Y, 'Date'[Date] )
VAR Result =
IF (
FirstDayWithData <= FirstDayInPeriod,
AVERAGEX (
Period1Y,
[Sales Amount]
)
)
RETURN
Result
For full explanation go the the best source from where I took it:
https://www.daxpatterns.com/standard-time-related-calculations/
Thanks SpartaBI.
I need the monthly avg revenue based on last 12 months
@chavanr so for months they also have a patten and this is the relevant calculation:
Sales AVG 1Y :=
VAR MonthsInRange = 12
VAR LastMonthRange =
MAX ( 'Date'[Year Month Number] )
VAR FirstMonthRange =
LastMonthRange - MonthsInRange + 1
VAR Period1Y =
FILTER (
ALL ( 'Date'[Year Month Number] ),
'Date'[Year Month Number] >= FirstMonthRange
&& 'Date'[Year Month Number] <= LastMonthRange
)
VAR Result =
IF (
COUNTROWS ( Period1Y ) >= MonthsInRange,
CALCULATE (
AVERAGEX ( Period1Y, [Sales Amount] ),
REMOVEFILTERS ( 'Date' )
)
)
RETURN
Result
https://www.daxpatterns.com/month-related-calculations/
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
113 | |
107 | |
106 | |
91 | |
67 |
User | Count |
---|---|
162 | |
133 | |
132 | |
93 | |
91 |