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