Forum Discussion

MEBOW's avatar
MEBOW
New Member
2 years ago
Solved

Date Filter for Measure

Hi, I have been digging thru a lot of posts for a solution and there are alot of great guidance but I keep getting stuck. 

 

I have two tables: 

 

Date Table 

Month Name AbbDay Name AbbYearWork DayWork DateWkDayHoliday
AprMon2024Monday, April 1, 202404/01/24TRUEFALSE
AprTue2024Tuesday, April 2, 202404/02/24TRUEFALSE
AprWed2024Wednesday, April 3, 202404/03/24TRUEFALSE

 

Then I have a time tracker table: 

 

UserGroupCategoryTime WorkedWork DayAfter HoursComments
John DoeABCAdministrative1Monday, April 1, 2024NoNotes to add
John DoeABCOperational Support5Monday, April 1, 2024NoNotes to add
John DoeABCProject2.5Monday, April 1, 2024NoNotes to add
John DoeABCUnplanned Operational Support2Monday, April 1, 2024NoNotes to add
John DoeABCPlanned Operational Support5Tuesday, April 2, 2024NoNotes to add
John DoeABCProject2.5Tuesday, April 2, 2024NoNotes to add
John DoeABCUnplanned Operational Support3Tuesday, April 2, 2024NoNotes to add
John DoeABCPlanned Operational Support8Wednesday, April 3, 2024NoNotes to add
John DoeABCPlanned Operational Support1Thursday, April 4, 2024NoNotes to add

 

I am creating a measure called "Working Days" where it's a count of the WkDay = TRUE when it's this month/year at the time table.   It just does not seem to see the page filters which is passing the Month Name = April and the Year  = 2024

 

WorkingDays =
CALCULATE(
COUNTROWS('Date Table'),
FILTER(
'Date Table',
'Date Table'[WkDay] = TRUE
&& YEAR('Date Table'[Work Date]) = YEAR(MAX('Date Table'[Year]))
&& LEFT(MONTH('Date Table'[Work Day]), 3) = LEFT(MONTH(MAX('Date Table'[Work Day])), 3)
)
)

 

Any suggsetions/directions would be appreciated.

 

 

Need Help
  • Kaviraj11's avatar
    Kaviraj11
    2 years ago

    Hi,

     

    Can you try this out:

     

    WorkingDays =
CALCULATE(
    COUNTROWS('Date Table'),
    FILTER(
        ALL('Date Table'),
        'Date Table'[WkDay] = TRUE
        && YEAR('Date Table'[Work Date]) = YEAR(MAX('Date Table'[Year]))
        && LEFT(MONTH('Date Table'[Work Day]), 3) = LEFT(MONTH(MAX('Date Table'[Work Day])), 3)
    )
)

     

1 Reply

  • Kaviraj11's avatar
    Kaviraj11
    Solution Sage
    2 years ago

    Hi,

     

    Can you try this out:

     

    WorkingDays =
CALCULATE(
    COUNTROWS('Date Table'),
    FILTER(
        ALL('Date Table'),
        'Date Table'[WkDay] = TRUE
        && YEAR('Date Table'[Work Date]) = YEAR(MAX('Date Table'[Year]))
        && LEFT(MONTH('Date Table'[Work Day]), 3) = LEFT(MONTH(MAX('Date Table'[Work Day])), 3)
    )
)