Forum Discussion
Helper IIIAverage Graph without the Last Month
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!
Helper IIIDear 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 =