topic Re: Average base on the range of month in DAX Commands and Tips

Average base on the range of month

jercks00 — Mon, 26 Aug 2024 07:50:46 GMT

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

Re: Average base on the range of month

Joe_Barry — Mon, 26 Aug 2024 08:26:38 GMT

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

Re: Average base on the range of month

Anonymous — Wed, 28 Aug 2024 07:10:56 GMT

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.