Running Total of working days

[Anonymous]

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???

[johnt75 · Accepted solution]

Try replacing

MIN('Date table'[Datum])

with

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

[johnt75]

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

[Anonymous]

Thanks for your Reply johnt75!

Unfortunatel it dowsn´t work with my file 😞

[johnt75]

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

[Anonymous]

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...

[johnt75 · Accepted solution]

Try replacing

MIN('Date table'[Datum])

with

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

[Anonymous · Accepted solution]

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