topic Re: Count number of working days current month in DAX Commands and Tips

Count number of working days current month

Rody — Mon, 15 Aug 2022 06:15:11 GMT

Hi all,

I am trying the build a sales report and for one metric I would like to show the average tonnes sold per day. In order to do this, I need to embed a dynamic calculation in my calendar table that count the amount of working days (mon-fri) past in the current month. This calculation needs to reset itself every single month. On top of that, I would like to save the total working days in the month when the month has completed.

I cannot wrap my head around this and hope that one of you can help me out.

Thanks

Re: Count number of working days current month

daXtreme — Mon, 15 Aug 2022 06:23:03 GMT

[# Working Days] = // calculated column var Today_ = TODAY() var CurrentMonth = 'Calendar'[Year-Month] var Result = SUMX( filter( 'Calendar' // This filters to the whole month that // is on the current line. 'Calendar'[Year-Month] = CurrentMonth // This one says not to count anything // that is in the future. && 'Calendar'[Date] <= Today_ ), 'Calendar'[Is working day] ) RETURN // Add 0 to Result if you want to see // 0 instead of BLANKS. Result

Re: Count number of working days current month

Rody — Mon, 15 Aug 2022 07:06:57 GMT

Fantastic! That did the job. Thanks heaps