topic Running Total of working days in DAX Commands and Tips

Running Total of working days

Anonymous — Tue, 13 Sep 2022 10:51:35 GMT

Hello BI-Community,

i´m struggling to get a running total of working days.

I have a Date Table that is already filtered based on working days. So only dates, which are a working date, are visible.

I even have a specific column in my date table that shows a 1 if it is a working date.

Let´s say we take todays date, i need the following result.

Anyonw who knows how to solve this???

Re: Running Total of working days

johnt75 — Tue, 13 Sep 2022 11:04:00 GMT

I think this should do it

Running total working days = VAR startDate = MIN ( 'Date'[Date] ) VAR endDate = MAX ( 'Date'[Date] ) VAR result = CALCULATE ( SUM ( 'Date'[Is working day] ), DATESBETWEEN ( 'Date'[Date], startDate, endDate ) ) RETURN result

Re: Running Total of working days

Anonymous — Tue, 13 Sep 2022 11:15:35 GMT

Thanks for your Reply johnt75!

Unfortunatel it dowsn´t work with my file 😞

Re: Running Total of working days

johnt75 — Tue, 13 Sep 2022 11:23:57 GMT

On the visual are you using the date column from your date table, or are you using a column from another table ? The way I had envisaged this working is that you would use the date column from your date table which would be set up with a one-to-many relationship to any other tables

Re: Running Total of working days

Anonymous — Tue, 13 Sep 2022 11:28:13 GMT

Iam using my date table to set up a slicer and everything else.

See attached the result from your measure. It doesn´t count them properly...

Re: Running Total of working days

johnt75 — Tue, 13 Sep 2022 11:33:41 GMT

Try replacing

MIN('Date table'[Datum])

with

CALCULATE( MIN('Date table'[Datum]), ALLSELECTED('Date table'))

Re: Running Total of working days

Anonymous — Tue, 13 Sep 2022 11:41:12 GMT

I just figured it out with a little bit of a workaround!

I tried to replace the measures like you mentioned before, but see attached the results.

Pre Rolling Working Days = COUNTROWS(FILTER('Date Table','Date Table'[Datum] < TODAY()&&'Date Table'[Working Day]=1))

Pre Rolling Working Days MTD =

TOTALMTD([Pre Rolling Working Days], 'Date Table'[Datum])

Thank you very much for your response anyway! It was the needed push to get the right result