Calculate current order level versus previous months average

[BugmanJ]

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 on, and when we get to June we reset the running order and the orders of the day go upto 7 and same for July where orders goto 8

Now say we have the following data for August:

Date	Ordes that Day	Running Total
01/07/2024	7	7
02/07/2024	6	13
03/07/2024	8	21
04/07/2024	7	28
05/07/2024	12	40
06/07/2024	6	46
07/07/2024	6	52

I am trying to calculate a measure which creates a % for that day when looking back at the average for ALL other months at that exact date.

So for the 5th August in the above dataset for running total, we would have 30 (from May), 35 (from June) and 40 (from july) giving us a  monthly average of 35 if you where looking at the data on the 5th of august. For August though, we have a total of 40 which is 40/35*100 = 114.2%

My current formular is this:

PercentDifference = 
VAR CurrentMonthOrders = 
    CALCULATE(
        DISTINCTCOUNT(Sales[Order No]),
        FILTER(
            ALL(DateTable),
            DateTable[Month] = MAX(DateTable[Month]) &&
            DateTable[Year] = MAX(DateTable[Year]) &&
            DateTable[Day] <= DAY(TODAY()) &&
            DateTable[Date] >= DATE(2024, 10, 31) -- I dont want to look pre November
        )
    )
VAR AverageOrdersUpToDay = 
    AVERAGEX(
        VALUES(DateTable[Month]),
        CALCULATE(
           DISTINCTCOUNT(Sales[Order No]),
            FILTER(
                ALL(DateTable),
                DateTable[Day] <= DAY(TODAY()) &&
                DateTable[Date] >= DATE(2024, 10, 31) -- I dont want to look pre November
            
            )
        )
    )
RETURN 
    ABS(DIVIDE(CurrentMonthOrders, AverageOrdersUpToDay, 0) - 1)

However, its giving the wrong result, its off by a few %

Why?

Two tables, "Sales" and "DateTable" linked together by "Datekey" (Which is INT number) with the only "DATE" being in the DateTable

A PBIX file holding the data can be found here - https://filebin.net/fkzpb9qpg3zv505w

There is also a txt file if you want to build your own, but you will need a datetable

[Anonymous]

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

[BugmanJ]

I have edited the above request with a link to a file! Hope this helps

[BugmanJ]

Hi, 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."

[Anonymous]

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

[Anonymous]

Hi @BugmanJ ,

Try this

Measure = 
VAR _monthname = SELECTEDVALUE(DateTable[Month Name Short])
VAR _result = FORMAT(DIVIDE([Running Total],[previousTotalAverage],0),"0.00")
RETURN
_monthname & " " & _result

Best Regards

[BugmanJ]

Thank you for sticking with this. However reducing it down to a single month or even months doesnt work.

[FarhanJeelani]

Hi  @BugmanJ ,

Try the below DAX:

PercentDifference = 
VAR CurrentMonth = MAX(DateTable[Month])
VAR CurrentYear = MAX(DateTable[Year])
VAR CurrentDay = DAY(TODAY())

-- Calculate the running total for the current month up to the current day
VAR CurrentMonthOrders = 
    CALCULATE(
        SUM(Sales[Orders]),
        FILTER(
            Sales,
            YEAR(Sales[Date]) = CurrentYear && 
            MONTH(Sales[Date]) = CurrentMonth && 
            DAY(Sales[Date]) <= CurrentDay
        )
    )

-- Calculate the average running total for the same day across all previous months
VAR AverageOrdersUpToDay = 
    AVERAGEX(
        FILTER(
            ALL(DateTable),
            DateTable[Month] < CurrentMonth || (DateTable[Month] = CurrentMonth && DateTable[Year] < CurrentYear)
        ),
        CALCULATE(
            SUM(Sales[Orders]),
            FILTER(
                Sales,
                YEAR(Sales[Date]) = DateTable[Year] && 
                MONTH(Sales[Date]) = DateTable[Month] && 
                DAY(Sales[Date]) <= CurrentDay
            )
        )
    )

-- Return the percentage difference
RETURN
    IF(
        AverageOrdersUpToDay <> 0,
        DIVIDE(CurrentMonthOrders, AverageOrdersUpToDay, 0) * 100,
        BLANK()
    )

 

Please mark this as solution if it helps you. Appreciate Kudos.

[BugmanJ]

Thanks, but this just gives me blanks like my current one does.

[FarhanJeelani]

@BugmanJ ,

sorry , can you try this?

PercentDifference =
VAR CurrentDay = DAY(TODAY()) -- Get the current day
VAR CurrentMonth = MONTH(MAX(DateTable[Date])) -- Get the current month
VAR CurrentYear = YEAR(MAX(DateTable[Date])) -- Get the current year

-- Running total for the current month up to the current day
VAR CurrentMonthOrders =
CALCULATE(
SUM(Sales[Orders]),
FILTER(
Sales,
YEAR(Sales[Date]) = CurrentYear &&
MONTH(Sales[Date]) = CurrentMonth &&
DAY(Sales[Date]) <= CurrentDay
)
)

-- Average running total for the same day across previous months
VAR AverageOrdersUpToDay =
AVERAGEX(
FILTER(
ALL(DateTable),
(YEAR(DateTable[Date]) < CurrentYear) ||
(YEAR(DateTable[Date]) = CurrentYear && MONTH(DateTable[Date]) < CurrentMonth)
),
CALCULATE(
SUM(Sales[Orders]),
FILTER(
Sales,
DAY(Sales[Date]) = CurrentDay &&
YEAR(Sales[Date]) = YEAR(DateTable[Date]) &&
MONTH(Sales[Date]) = MONTH(DateTable[Date])
)
)
)

-- Calculate the percentage difference
RETURN
IF(
AverageOrdersUpToDay > 0,
DIVIDE(CurrentMonthOrders, AverageOrdersUpToDay, 0) * 100,
BLANK()
)
output:

For August 5th:

Running Total: Current month (August) = 40.

Average Running Total: Previous months = 30+35+403=35\frac{30 + 35 + 40}{3} = 35330+35+40​=35.

Percent Difference: 4035×100=114.2%\frac{40}{35} \times 100 = 114.2\%3540​×100=114.2%.

Please mark this as solution if it helps. Appreciate Kudos.

[BugmanJ]

Whilst your maths is correct, the actual equation above gives some very large numbers!!!

I have edited the above request with a link to a file! Hope this helps