Average Graph without the Last Month

[Young_G_Han]

  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])))

[Young_G_Han · Accepted solution]

  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!

[v-heq-msft]

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

[Young_G_Han]

  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.

[lbendlin]

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.

[Young_G_Han]

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?