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

 

 

"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!

5 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

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

      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?

  • 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.
  • Young_G_Han's avatar
    Young_G_Han
    Icon for Helper III rankHelper III
    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!