cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## Calculate average revenue based on last 12 months

Hi there,

I need to calculate average revenue based on last 12 month.

What would be the simple measure to calculate the same.

Cheers,

1 ACCEPTED SOLUTION
Community Champion

@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

3 REPLIES 3
Community Champion

@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/

Resolver I

Thanks SpartaBI.

I need the monthly avg revenue based on last 12 months

Community Champion

@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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.