Forum Discussion

HY2024's avatar
HY2024
Frequent Visitor
1 year ago
Solved

Calculate current month sales average

Hello,   I would like to calculate the average sales per day for the current month first and then compare it to the same period the year before. I have a dataset with the sales by day, so basicall...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi HY2024 ,

     

    I have tested  Bibiano_Geraldo's measure. I think STARTOFMONTH() and DATEADD() could work with specific columns, it will return error if you use TODAY().  Here I suggest you to try EOMONTH()

    Avg Sales Per Day Current Month = 
DIVIDE(
    CALCULATE(
        SUM('Table'[Sales]),
        DATESBETWEEN(
            'Table'[Date],
            EOMONTH(TODAY(),-1)+1,
            TODAY()
        )
    ),
    DATEDIFF(
        EOMONTH(TODAY(),-1)+1,
        TODAY(),
        DAY
    ) + 1,
    0
)
    Avg Sales Per Day Same Period Last Year = 
DIVIDE(
    CALCULATE(
        SUM('Table'[Sales]),
        DATESBETWEEN(
            'Table'[Date],
            EOMONTH(TODAY(),-13)+1,
            EOMONTH(TODAY(),-13)+ DAY(TODAY())
        )
    ),
    DATEDIFF(
        EOMONTH(TODAY(),-13)+1,
        EOMONTH(TODAY(),-13)+ DAY(TODAY()),
        DAY
    ) + 1,
    0
)

    You can download my sample file to learn more details.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

Bibiano_Geraldo's avatar
Bibiano_Geraldo
Icon for Super User rankSuper User
1 year ago

Hi HY2024 ,

You can achieve your goal by this DAX for avg sales per day current month

Avg Sales Per Day Current Month = 
DIVIDE(
    CALCULATE(
        SUM(Sales[Quantity]),
        DATESBETWEEN(
            Sales[Date],
            STARTOFMONTH(TODAY()),
            TODAY()
        )
    ),
    DATEDIFF(
        STARTOFMONTH(TODAY()),
        TODAY(),
        DAY
    ) + 1,
    0
)

Same period last Year:

Avg Sales Per Day Same Period Last Year = 
DIVIDE(
    CALCULATE(
        SUM(Sales[Quantity]),
        DATESBETWEEN(
            Sales[Date],
            DATEADD(STARTOFMONTH(TODAY()), -1, YEAR),
            DATEADD(TODAY(), -1, YEAR)
        )
    ),
    DATEDIFF(
        DATEADD(STARTOFMONTH(TODAY()), -1, YEAR),
        DATEADD(TODAY(), -1, YEAR),
        DAY
    ) + 1,
    0
)

 

% Variance:

Percentage Change = 
DIVIDE(
    [Avg Sales Per Day Current Month] - [Avg Sales Per Day Same Period Last Year],
    [Avg Sales Per Day Same Period Last Year],
    0
)