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...
Hi FarhanJeelani,thanks for the quick reply, I'll add more.
Hi BugmanJ ,
Try this
Measure =
VAR _date = SELECTEDVALUE('Table'[Date])
VAR _day = DAY(SELECTEDVALUE('Table'[Date]))
VAR _total = AVERAGEX(FILTER(ALL('Table'),DAY([Date]) = _day && 'Table'[Date] < _date),[Running Total])
RETURN DIVIDE(MAX('Table'[Running Total]),_total)
Best Regards
BugmanJHi, 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 you
- BugmanJ
I have edited the above request with a link to a file! Hope this helps