topic Re: Average Graph without the Last Month in DAX Commands and Tips

Average Graph without the Last Month

Young_G_Han — Mon, 04 Mar 2024 00:19:41 GMT

Dear Kudos

I had great help from this community and got many good Dax measures.
One of them helped me to draw the following average graph.
I could compare average sales per month year by year.
But there is one problem with this one: I have much less sales at the beginning of the month than in the previous months.

So the average becomes much less... during the beginning of a year, it affects a lot.

I want to eliminate the latest month from the calculation but I couldn't...

Can you improve the Dax measure?

The measure I used is the following.

calculate(averagex(values(date[month]), calculate([sales])), datesytd(endofyear(date[date])))

Re: Average Graph without the Last Month

lbendlin — Mon, 04 Mar 2024 02:03:40 GMT

You add a filter that excludes the current month.

Your formula looks a bit, uhm, elaborate. Recommend you simplify it by using either CALCULATE(AVERAGE()) or AVERAGEX(), but not both together.

Re: Average Graph without the Last Month

Young_G_Han — Mon, 04 Mar 2024 02:08:54 GMT

Dear Ibendlin

Thank you for your advice.

My problem is that my Dax ability is not good enough to edit the measures. How can I filter the measure?

My graph doesn't have any date filter, it shows all years and months.

Can you help?

Re: Average Graph without the Last Month

Anonymous — Fri, 29 Mar 2024 08:35:00 GMT

Hi Young_G_Han ,
Here some steps that I want to share, you can check them if they suitable for your requirement.
Here is my test data:

Create a year column

Year = YEAR('Date'[DATE])

Create a average column

Average Sales by Year = CALCULATE( AVERAGE('Date'[Sales]), FILTER( ALLEXCEPT('Date','Date'[Year]), 'Date'[DATE] < DATE(2024,2,1) ) )

Final outtput

Best regards,

Albert He

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

Re: Average Graph without the Last Month

Young_G_Han — Sun, 31 Mar 2024 23:09:18 GMT

Dear v-heq-msft

Thank you for the reply.

Because my sales data has the daily sales, the measure returns different result.

I have changed your measure like below, it was working in the sample BI that you attached.

Average Sales by Year =

CALCULATE(

AVERAGE('Date'[Sales]),

FILTER(

ALLEXCEPT('Date','Date'[Year]),

'Date'[DATE] < MAX('Date'[DATE])

)

But in my original BI report, it shows much smaller values since the measure calculates average daily sales in a month.

I have to make an average of monthly sales...

Do I have any chance to improve your solution???

Thanks in advance.

Re: Average Graph without the Last Month

Young_G_Han — Tue, 02 Apr 2024 02:49:27 GMT

Dear All

I successfully made a measure that will show the monthly average sales of each year, excluding the latest month.

Why do I need it?

When we are in the middle of a month, the average would be smaller compared to the previous year due to fewer sales of the current month.

So, I used a filter to exclude current month sales, the logic is simple and needs two measures.

Before to use this measure, you need to add a column in the DATE table.

YEAR&MONTH = FORMAT(DATE[DATE], "YYYYMM")

Daily Sales =
CALCULATE(SUMX(DATA, DATA[VALUE]), FILTER(DATE, DATE[YEAR&MONTH] <> FORMAT(TODAY(), "YYYYMM")))

Then

Average Month =

IF(

SELECTEDVALUE('_DATE'[YEAR&MONTH]) > FORMAT(TODAY(), "YYYYMM"), BLANK(),

CALCULATE(AVERAGEX(VALUES('_DATE'[MONTH]),

CALCULATE([Daily Sales])), DATESYTD(ENDOFYEAR('_DATE'[DATE])))

)

This will show the average sales per month in each year.

Cheers for users!