Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
jessimica1018
Helper I
Helper I

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!

4 REPLIES 4
daXtreme
Solution Sage
Solution Sage

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.

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

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:

vyinliwmsft_0-1668062170722.png

 

vyinliwmsft_1-1668062170725.png

 

vyinliwmsft_2-1668062170726.png

 

 

 

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.

This is so insanely close to what we need!! The only issue we've ran into with this is when the decline date is after the accepted data. It's not showing any counts for the declines (I'm thinking thats why? maybe?)   It isn't a super common occurance but it does happen.
We have tried adjusting the decline formula a bit but nothing on our end is making this work.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.