topic Re: Sales / Business Day for current month in DAX Commands and Tips

Sales / Business Day for current month

pwe5000 — Wed, 02 Mar 2022 22:39:25 GMT

Hello!

I have a measure that calculates sales per business day for each month. It just divides the total sales for the month by the number of business days in the month. Here is the DAX for that measure:

Sales/Bus Day = DIVIDE([Sales, All Channels], CALCULATE( SUM('Date Table'[Workday Flag]), 'Date Table'[Workday Flag] IN { 1 } ))

The expression,

CALCULATE(

SUM('Date Table'[Workday Flag]),

'Date Table'[Workday Flag] IN { 1 }

ensures that I'm measuring sales per business day and not simply sales per day.

This works great if the month is already finished, but for the current month, it shows a very low figure, because not all the sales for the month have happened yet. How can I make a single measure that works both for months that have finished and for the current month?

Re: Sales / Business Day for current month

amitchandak — Thu, 03 Mar 2022 03:21:02 GMT

pwe5000 , Try a measure like

Sales/Bus Day =
DIVIDE([Sales, All Channels], CALCULATE(
SUM('Date Table'[Workday Flag]),
filter('Date Table','Date Table'[Workday Flag] IN { 1 } && 'Date Table'[Date] < today())
))

calculate(averageX(values('Date Table'[Date]),[Sales, All Channels]) ,'Date Table'[Workday Flag] IN { 1 } )

Re: Sales / Business Day for current month

pwe5000 — Thu, 03 Mar 2022 14:28:34 GMT

amitchandak

A simple and elegant solution. Works like a charm. Thanks very much!