Forum Discussion

azlocal21's avatar
azlocal21
Frequent Visitor
4 years ago
Solved

Date Filtering Issues

Hello everyone!

Super new, and mostly self taught in Power Bi Destkop. I have gotten a lot of help building measures from this forum but I have not been able to find the help I need for this issue.

I'm trying to build a matrix that looks like this:


See a sample table that matches this desired visual above to this post to make it easier to help at the bottom of the page.


Here's one of the measures I have tried:

Most Recent Hire Class = CALCULATE ( MAX ( Table1[Start_Date] ) , Table1[Start_Date]<=TOday() )


Also this one (and every variation I can think of):

Most Recent Filter = VAR _MostRecentFilter = MAX('Table1'[Start_Date])
Return
MINX(
Filter(
('Table1'),'Table1'[Start_Date]=MIn('Table1'[Start_Date]) &&
'Table1'[Start_Date]<=Today()
),
'Table1'[Start_Date]
)
 
The issue I'm having is it's not isolating only the exams needed and only the most recent start date.

I can create the matrix I'm looking for using Filters on the visuals manually but that would mean I'd have to update the Filter every time a new start date occurs. I really want to avoid this as much as possible. I have not been able to find a measure that can pull the data based on the most recent start date and input all the numbers in relation to that start date.


Any help would be greatly appreciated as I've reached my knowledge limitation.


Sample Table:

SiteStart_DateExams_NeededCurrent_StatusFinish_Date

TX

2/22/2022

B,CB 1st Attempt15-Jul
TX2/22/2022B,CB 1st Attempt15-Jul
AZ2/22/2022B,CB 1st Attempt15-Jul
TX2/22/2022B,CB 2nd Attempt15-Jul
NC2/22/2022B,CB 2nd Attempt15-Jul
NC2/22/2022B,CC 1st Attempt1-Jun
NC2/22/2022B,CC 1st Attempt1-Jun
TX2/22/2022NoneExams Complete1-Apr
AZ3/7/2022CC 1st Attempt10-Jun
AZ3/21/2022A,B,CA 1st Attempt10-Aug
NC3/21/2022A,B,CA 1st Attempt10-Aug
NC3/21/2022A,B,CA 1st Attempt10-Aug
TX3/21/2022A,B,CA 1st Attempt10-Aug
NC3/21/2022A,B,CA 1st Attempt10-Aug
NC3/21/2022A,B,CA 1st Attempt10-Aug
PA3/21/2022A,B,CA 1st Attempt10-Aug
PA3/21/2022A,B,CA 1st Attempt10-Aug
AZ3/21/2022B,CB 1st Attempt15-Jul
AZ3/21/2022B,CB 1st Attempt15-Jul
AZ3/21/2022B,CB 1st Attempt15-Jul
AZ3/21/2022B,CB 1st Attempt15-Jul
NC3/21/2022B,CB 1st Attempt15-Jul
TX3/21/2022B,CB 1st Attempt15-Jul
AZ3/21/2022B,CB 1st Attempt15-Jul
AZ3/21/2022B,CB 1st Attempt15-Jul
AZ3/21/2022B,CB 1st Attempt15-Jul
AZ3/21/2022B,CB 1st Attempt15-Jul
NC3/21/2022B,CB 1st Attempt15-Jul
NC3/21/2022B,CB 1st Attempt15-Jul
NC3/21/2022B,CB 1st Attempt15-Jul
PA3/21/2022B,CB 1st Attempt15-Jul
TX3/21/2022B,CB 1st Attempt15-Jul
TX3/21/2022B,CB 1st Attempt15-Jul
TX3/21/2022B,CB 1st Attempt15-Jul
TX3/21/2022B,CB 1st Attempt15-Jul
TX3/21/2022B,CB 1st Attempt15-Jul
TX3/21/2022B,CB 1st Attempt15-Jul
NC3/21/2022B,CB 1st Attempt15-Jul
PA3/21/2022B,CB 1st Attempt15-Jul
TX3/21/2022B,CC 1st Attempt15-Jul
AZ3/21/2022NoneExams Complete10-May
TX3/21/2022NoneExams Complete10-May
AZ4/4/2022B,CPrehire 
AZ4/4/2022B,CPrehire 
AZ4/4/2022A,B,CPrehire 
AZ4/4/2022A,B,CPrehire 

 

Need Help
  • amitchandak's avatar
    amitchandak
    4 years ago

    azlocal21 , Try a measure like

     


    var _max = VAR _MostRecentFilter = MAXX( Filter( allselected('Table1') , 'Table1'[Start_Date]<=Today()) ,'Table1'[Start_Date])
    Return
    calculate(count('Table1'[Start_Date]), filter('Table1', 'Table1'[Start_Date] =__max))

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    azlocal21 , Try a measure like

     


    var _max = VAR _MostRecentFilter = MAXX( Filter( allselected('Table1') , 'Table1'[Start_Date]<=Today()) ,'Table1'[Start_Date])
    Return
    calculate(count('Table1'[Start_Date]), filter('Table1', 'Table1'[Start_Date] =__max))

    • azlocal21's avatar
      azlocal21
      Frequent Visitor
      4 years ago

      When I tried that this is the error message it gives me.

      The syntax for ')' is incorrect. (DAX(var _max = VAR MostRecentFilter = MAXX( Filter( allselected('Table1') , 'Table1'[Start_Date]<=Today()) ,'Table11'[Start_Date])Returncalculate(count('Table1'[Start_Date]), filter('Table1', 'Table1'[Start_Date] =_max)))).


      **Update**

      I removed the VAR _MostRecentFilter from the measure and that works great for giving me the number or most recent hires for the most recent start date.

      The problem I am having now is that the finish date is pulling the earliest date for every exam path.