Calculate average revenue based on last 12 months

[chavanr]

Hi there,

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

What would be the simple measure to calculate the same.

Cheers,

[SpartaBI]

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

      

Showcase Report – Contoso By SpartaBI

[chavanr]

Thanks SpartaBI.

I need the monthly avg revenue based on last 12 months

[SpartaBI · Accepted 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/

      

Showcase Report – Contoso By SpartaBI