Forum Discussion

BugmanJ's avatar
BugmanJ
Icon for Helper V rankHelper V
1 year ago

Calculate current order level versus previous months average

Hi,

If we have the following data:

DateOrdes that DayRunning Total
01/05/202466
02/05/2024612
03/05/2024618
04/05/2024624
05/05/2024630

 

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:

DateOrdes that DayRunning Total
01/07/202477
02/07/2024613
03/07/2024821
04/07/2024728
05/07/20241240
06/07/2024646
07/07/2024652


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

Need Help

11 Replies

  • FarhanJeelani's avatar
    FarhanJeelani
    Icon for Super User rankSuper User
    1 year ago

    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's avatar
      BugmanJ
      Icon for Helper V rankHelper V
      1 year ago

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

      • FarhanJeelani's avatar
        FarhanJeelani
        Icon for Super User rankSuper User
        1 year ago

        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} = 35

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      BugmanJ
      Icon for Helper V rankHelper V
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        1 year ago

        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's avatar
      BugmanJ
      Icon for Helper V rankHelper V
      1 year ago

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