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 basically I would like to calculate the average quantities of pieces that have been sold by day for the current month. and then compare it in persentage to the same period last year

1. I need to take all the sales from the first day of the current month until today (let say today the 22nd of january) and then calculate the average sales per day.

2. compare in percentage vs same period last year

 

can you please help me

thank you

best regards

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.

     

2 Replies

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super 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
)
  • Anonymous's avatar
    Anonymous
    Not applicable
    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.