topic Re: Date diff Working Days to Today with filter in DAX Commands and Tips

Date diff Working Days to Today with filter

ctedesco3307 — Mon, 29 Aug 2022 14:04:43 GMT

I have a table that has all the working days identifed for a project between the start and end date. I need a measure to add up all the working days for each project from Start date to today, Thanks in advance

Re: Date diff Working Days to Today with filter

amitchandak — Mon, 29 Aug 2022 14:50:47 GMT

ctedesco3307 ,

A new column

networkdays([Start Date], Today(),1)

https://amitchandak.medium.com/power-bi-dax-function-networkdays-5c8e4aca38c

Re: Date diff Working Days to Today with filter

ctedesco3307 — Mon, 29 Aug 2022 15:26:27 GMT

amitchandak thank you but the function needs to sum the workingday column in the table if workingday = 1 -- not just omit saturday and sunday - we have company holidays also

Re: Date diff Working Days to Today with filter

amitchandak — Tue, 30 Aug 2022 06:19:46 GMT

ctedesco3307 , Please refer blog I shared complete information on how to use the holiday calendar

https://amitchandak.medium.com/power-bi-dax-function-networkdays-5c8e4aca38c

Re: Date diff Working Days to Today with filter

Anonymous — Thu, 01 Sep 2022 08:24:13 GMT

Hi ctedesco3307 ,

I suggest you to try this code to create a measure.

Measure = VAR _MINDATE = MIN(MAX('Table'[End Date]),TODAY()) VAR _COUNT = CALCULATE(SUM('Table'[Working Day]),FILTER('Table','Table'[MSSContractDay]<=_MINDATE))+0 RETURN _COUNT

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.

Re: Date diff Working Days to Today with filter

Anonymous — Tue, 31 Oct 2023 07:36:03 GMT

hi Rico,

am using your DAX statement like this:

DaysDiff =

VAR _MINDATE= MIN(MAX('KPI Delivery'[InvoiceDate]), TODAY())

VAR _COUNT = CALCULATE(SUM('KPI Delivery'[InvoiceWeekendDay]),FILTER('KPI Delivery', 'KPI Delivery'[InvoiceDate]<=_MINDATE))+0

RETURN

_COUNT

The measure seems to be working but as soon as i add the measure to a visual i get this error message:
MdxScript(Model (5,29) Calculation error in measure TABLE [DaysDiff]; the function SUM cannot work with values of type string. Can you help. thnx!!

Re: Date diff Working Days to Today with filter

Anonymous — Tue, 31 Oct 2023 07:42:03 GMT

hi Rico,

forget about my question, found the issue and solved it.

The measure is working!

thnx!!!!