Average base on the range of month

[jercks00]

Hello is this possible to do in power Bi.

I have the sum of sales per seller in a month from  July 2023 to July 2024.

What I want in my matrix table is the sum of Sales per Month and also show the Average sales from July 2023 to June 2024 Just like what I did in Excel

 

[Joe_Barry · Accepted solution]

Hi @jercks00 

Presuming that you have a calendar table in your model, this measure will show you the last 12 months, you can adapt it to other months too.

Sales R3M =
VAR NumOfMonths = 12
VAR LastCurrentDate =
    MAX ( 'Calendar'[Date] )
VAR Period =
    DATESINPERIOD ( 'Calendar'[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR Result =
    CALCULATE (
        AVERAGEX (
            VALUES ( 'Calendar'[Date] ),
            [Total Sales]
        ),
        Period
    )
VAR FirstDateInPeriod = MINX ( Period, 'Calendar'[Date] )
VAR LastDateWithSales = MAX ( Sales[Order Date] ) ///Enter the date that you want to calculate on
RETURN
    IF ( FirstDateInPeriod <= LastDateWithSales, Result )

 Hope this helps
Joe

[Anonymous · Accepted solution]

Hi @jercks00 

Thank you very much Joe_Barry for your prompt reply.

Tables in power BI are presented differently than excel, and you can't present them like excel.

Here I will offer some other ideas to help you solve the problem.

You need a data table, and a copy table. And establish a relationship between the two tables.

Here's some dummy data

“Table”

“copy Table”

pivot the Month and amount columns in the copy table in the power query.

Create a relationship between two tables in the Power BI Desktop.

Create measures.

Total = 
CALCULATE(
    SUM('Table'[Amount]),
    FILTER(
        ALL('Table'),
        'Table'[Name] = MAX('Table'[Name])
    )
)

Average = 
CALCULATE(
    AVERAGE('Table'[Amount]),
    FILTER(
        ALL('Table'),
        'Table'[Name] = MAX('Table'[Name])
    )
)

Create a table visual. And here is the result.

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Joe_Barry · Accepted solution]

Hi @jercks00 

Presuming that you have a calendar table in your model, this measure will show you the last 12 months, you can adapt it to other months too.

Sales R3M =
VAR NumOfMonths = 12
VAR LastCurrentDate =
    MAX ( 'Calendar'[Date] )
VAR Period =
    DATESINPERIOD ( 'Calendar'[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR Result =
    CALCULATE (
        AVERAGEX (
            VALUES ( 'Calendar'[Date] ),
            [Total Sales]
        ),
        Period
    )
VAR FirstDateInPeriod = MINX ( Period, 'Calendar'[Date] )
VAR LastDateWithSales = MAX ( Sales[Order Date] ) ///Enter the date that you want to calculate on
RETURN
    IF ( FirstDateInPeriod <= LastDateWithSales, Result )

 Hope this helps
Joe