number of days x month ago, with slicer for working days

[y5famfnatudu]

Hello, I wish you all a wonderful year.

I have this measure which calculates the number of days in the whole previous month

Days m01 = 
VAR __MonthsBack = 1
VAR __Day =
    CALCULATE (
        COUNTROWS ( 'Date 2' ),
        DATESINPERIOD (
            'Date 2'[Date],
            EOMONTH ( TODAY (), - __MonthsBack ),
            -1,
            MONTH
        )
    )
RETURN
    __Day

I have a slicer from the Date Table which is coming from the column Date[IsWorkingDay]
Unfortunately, whatever value I select from the slicer (0, 1), the value of the measure is showing 31 (number of days in DEC, which is last month)
How can I make this measure be dynamic to filter based on the slicer?

Best regards,
Simon

[johnt75 · Accepted solution]

Try

Days m01 =
VAR __MonthsBack = 1
VAR __Day =
    CALCULATE (
        COUNTROWS ( 'Date 2' ),
        KEEPFILTERS (
            DATESINPERIOD (
                'Date 2'[Date],
                EOMONTH ( TODAY (), - __MonthsBack ),
                -1,
                MONTH
            )
        )
    )
RETURN
    __Day

[y5famfnatudu]

Perfect, thank you @johnt75 so much, but what was the point of adding Keepfilters, I didn't quite get it?

[johnt75]

If 'Date 2' is marked as a date table, or also I think if the date column is used in a one-to-many relationship, when you apply a filter to the date column then all other filters on the table are removed automatically, to simplify time intelligence type calculations. If you want to manipulate the dates but retain other filters on e.g. working days or days of the week then you need to use KEEPFILTERS.

[rohit1991]

To make your measure dynamic based on the slicer for Date[IsWorkingDay], update it as follows:

Days m01 =
VAR __MonthsBack = 1
VAR __StartDate = EOMONTH(TODAY(), -__MonthsBack) + 1
VAR __EndDate = EOMONTH(TODAY(), -__MonthsBack)
RETURN
    CALCULATE(
        COUNTROWS('Date 2'),
        'Date 2'[Date] >= __StartDate && 'Date 2'[Date] <= __EndDate
    )

This ensures the slicer filters are respected, dynamically adjusting the count of days based on IsWorkingDay.

[y5famfnatudu]

And even when I fix the date range, it shows 31 (number of days in in December, which is the previous month) all the time, no matter which value in the slicer is selected

[y5famfnatudu]

Thank you, but this shows blank, no values are showing no matter which slicer value I select