Forum Discussion

Young_G_Han's avatar
Young_G_Han
Icon for Helper III rankHelper III
2 years ago
Solved

Average Graph without the Last Month

  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...
"Need Help"
  • Young_G_Han's avatar
    Young_G_Han
    2 years ago

      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!
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    Young_G_Han
    Icon for Helper III rankHelper III
    2 years ago

      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.