Forum Discussion
How to calculate difference between two columns and applying a filter
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.
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
5 Replies
- v-easonf-msft
Community Support
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 - ddpl
Solution Sage
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.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.
