Forum Discussion
BugmanJ
Helper V
Helper VCalculate current order level versus previous months average
Hi, If we have the following data: Date Ordes that Day Running Total 01/05/2024 6 6 02/05/2024 6 12 03/05/2024 6 18 04/05/2024 6 24 05/05/2024 6 30 And so...
BugmanJHelper V
Helper VHi, thanks for the assist, but doesnt work as Running Total is not a column, its a measure, causing the MAX to have the error "The MAX function only accepts a column reference as the argument number 1."
Hi BugmanJ ,
Use the following DAX expressions to create measures
Running Total =
VAR _date = SELECTEDVALUE(DateTable[Date])
VAR _month = SELECTEDVALUE(DateTable[MonthKey])
VAR _table = SUMMARIZE(ALL('Sales'),'Sales'[Date],'DateTable'[MonthKey],"Count",COUNT(Sales[Order Number]))
RETURN SUMX(FILTER(_table,[Date] <= _date && [MonthKey] = _month),[Count])previousTotalAverage =
VAR _day = DAY(SELECTEDVALUE(DateTable[Date]))
VAR _month = SELECTEDVALUE('DateTable'[MonthKey])
VAR _table = SUMMARIZE(ALL(DateTable),[Date],[MonthKey],"Result",[Running Total])
VAR _previous = AVERAGEX(FILTER(_table,DAY([Date]) = _day && [MonthKey] < _month),[Result])
RETURN IF(ISBLANK(_previous),0,_previous)Measure =
DIVIDE([Running Total],[previousTotalAverage],0)
Best Regards
- BugmanJ
Hi,
Wow this is great, but I have a slight nuance, If i wanted to display just the month, it gives me "0.00".
How can I fix this so that it shows me for that date?
E.g. Lets say we are on the 7th of Jan, so the value would show: Jan 0.98????
Thank youHi BugmanJ ,
Try this
Measure = VAR _monthname = SELECTEDVALUE(DateTable[Month Name Short]) VAR _result = FORMAT(DIVIDE([Running Total],[previousTotalAverage],0),"0.00") RETURN _monthname & " " & _resultBest Regards
- BugmanJ
Thank you for sticking with this. However reducing it down to a single month or even months doesnt work.