Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
chavanr
Resolver I
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
SpartaBI
Community Champion
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

 

https://www.daxpatterns.com/month-related-calculations/


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

View solution in original post

3 REPLIES 3
SpartaBI
Community Champion
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/



2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

Thanks SpartaBI.

 

I need the monthly avg revenue based on last 12 months

SpartaBI
Community Champion
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

 

https://www.daxpatterns.com/month-related-calculations/


2022-05-19 17_30_22-Re_ Need help on DAX function with measure vs colu... - Microsoft Power BI Commu.png


Full-Logo11.png

SpartaBI_3-1652115470761.png   SpartaBI_1-1652115142093.png   SpartaBI_2-1652115154505.png

Showcase Report – Contoso By SpartaBI

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.