How to calculate difference between two columns and applying a filter

[srana]

Hi all,

I have 3 columns i.e

1st column "Dates" that consists of dates from 1st April 2022 till 23rd March 2023.

2nd column that has only one date that keeps updating for now it is 18th May 2022.

3rd column is present that has list of working day that is marked " Yes" and "No" respective to the dates in "Dates" column.

I want to calculate how many days of working is completed from 1st April till 18th May 2022 excluding holidays.

[v-easonf-msft · Accepted solution]

Hi, @srana 

If your report contains other slicers to filter the data, try measure formula like:

Completed Working Days = 
CALCULATE (
    COUNT ( 'Table'[1st column] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[1st column] <= MAX ( 'Table'[2nd column] )
            && 'Table'[3rd column] = "Yes"
    )
)

Best Regards,
Community Support Team _ Eason

[tamerj1]

Hi @srana 
Please try

Completed Working Days =
VAR RefDate =
    MAX ( TableName[Ref. Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( TableName[Date] ),
        TableName[Working Day] = "Yes",
        TableName[Date] <= RefDate
    )

[ddpl]

@srana Can you show sample of your data set...?

[ddpl]

Create a Measure, which gives you count of working days upto 18th May, 2022

Days of Working =

     CALCULATE(

          COUNTROWS('Table'),

          'Table'[3rd Column]="Yes",

          'Table'[Dates]<='Table'[2nd Column]

)

Hope it will work for you.

[srana]

It is giving error saying " The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.