Forum Discussion

jessimica1018's avatar
jessimica1018
Helper II
3 years ago

Dynamic counts with date filter

Hey!

My team has been challeneged to represent counts in a dynamic way (I think, I'm not actually sure of the best way to pull this off).

 

We have data for declined contracts vs accepted contracts. There are occassion where a candidate will decline and soon after change their mind and accept. 

 

Name: Candidate Table

CandidateRecruiterDeclinedAccepted
BobJane Doe7/8/20227/11/2022
Sally Jane Doe 7/14/2022
NormanJohn Doe 7/15/2022
GuyJohn Doe 7/9/2022

 

What they are looking for is counts by recruiter name

               The rough part: if they have both a declined and accepted date

                      if the date range filter include both dates then only the "Final" or last contract status is counted 

Date Filter7/8/22 - 7/15/22
 DeclinesAccepted
Jane Doe 2
John Doe 2

 

                       if the date range filter doesn't include both then it counts the one in that range

Date Filter7/8/22 - 7/10/22
 DeclinesAccepted
Jane Doe1 
John Doe 1

 

Date Filter7/10/22 - 7/18/22
 DeclinesAccepted
Jane Doe 2
John Doe 

1

 

 

I hope that makes sense! I was thinking a parameter is needed? or am I not going down the right path for this? I wasn't sure if a bunch of "if" statements was a performance friendly way to go either. 

 

Any help would be fantastic!

Need Help
Tips and Tricks

4 Replies

  • daXtreme's avatar
    daXtreme
    Solution Sage
    3 years ago

    Is it possible that a candidate rejects and then accepts on the same day? Because if that's the case, you've got a problem. You'll have to also store the time of such events.

    • jessimica1018's avatar
      jessimica1018
      Helper II
      3 years ago

      There is a chance - we do have the date/time the action happened.

      • v-yinliw-msft's avatar
        v-yinliw-msft
        Community Support
        3 years ago

        Hi jessimica1018 ,

         

        You can try this method:

        New a date table:

        Date = CALENDAR(MIN('Candidate Table'[Declined]), MAX('Candidate Table'[Accepted]) )

         

        In the candidate table, create two measures:

        Accept =
CALCULATE (
    COUNT ( 'Candidate Table'[Accepted] ),
    FILTER (
        'Candidate Table',
        'Candidate Table'[Accepted] >= MIN ( 'Date'[Date] )
            && 'Candidate Table'[Accepted] <= MAX ( 'Date'[Date] )
    )
)

        Declines =
VAR _minormax =
    IF (
        SELECTEDVALUE ( 'Candidate Table'[Declined] )
            < SELECTEDVALUE ( 'Candidate Table'[Accepted] ),
        BLANK (),
        CALCULATE (
            COUNT ( 'Candidate Table'[Declined] ),
            FILTER (
                'Candidate Table',
                'Candidate Table'[Declined] <= MAX ( 'Date'[Date] )
                    && 'Candidate Table'[Declined] >= MIN ( 'Date'[Date] )
            )
        )
    )
RETURN
    IF ( MIN ( 'Candidate Table'[Accepted] ) >= MAX ( 'Date'[Date] ), _minormax )

        Use the date table to be a slicer.

        The result is:

         

         

         

         

         

        Hope this helps you.

        And here is my PBIX file.

         

        Best Regards,

        Community Support Team _Yinliw

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.