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 contract...
Need Help
Tips and Tricks
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.

    • jessimica1018's avatar
      jessimica1018
      Helper II
      3 years ago

      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.