Calculate Appointments Between 2 Dates Question

DAX DAX Calculations
1

Hi Fellow Power BI Users -

I am trying to work the number of appointments between 2 dates.

The issue arises if the staff member has changed the amount of days worked.

image

What I want to do is for Staff member ‘J’, I want it to calculate Target Appts between the start and the end date and if no end date exists return Target Appts on or after start date

I’ve reviewed the Time Intelligence course but unable to figure out

This is what I have so far

Target Appts = 
SUMX (
    SUMMARIZE (
        'Access Active Advisers',
        'Access Active Advisers'[Adviser],
        "Expected Appts", [Predicted Adviser Appt]
    ),
    [Expected Appts]
)



Appts =
CALCULATE (
    [Target Appts],
    FILTER (
        VALUES ( Dates[Date] ),
        COUNTROWS (
            FILTER (
                'Access DB Adviser Appt Target',
                Dates[Date] >= 'Access DB Adviser Appt Target'[Start]
                    && Dates[Date] <= 'Access DB Adviser Appt Target'[End]
            )
        )
    )
)

Thanks

2

When working with multiple dates the model is the first part that needs to be right.

You’ll want to review this video for that.

Then you’ll want to apply some of the logic from this DAX pattern called event is progress.

Check out the logic here.

I’m sure it will make more sense with an understanding of both of these.

See how you go after this.

3

Sam,

Thanks for this!! Exactly what I am looking to do.

This has give rise to another issue though. if you observe in the screenshot, not all staff member’s have an end date.

By connecting the dates table to the end date column, i now have blanks showing in my dates filter. is there a way to avoid this happening besides setting the ‘is not blank’ on the page level filter?

image

Thanks

4

I would honestly just use the page level filter on this one and be done with it. I can’t think of a way to adjust by another means.

5

Ok,

I’ve reviewed your tutorials and am having a couple of issues trying to work out the total end result.

I am trying to find out first, what is the number of working days before the change and after the change in the rostered days:

I just cannot work out why the total values is incorrect for the Previous Rostered Days & New Rostered Days

New Rostered Days =
CALCULATE (
    [NSW Current Working Days],
    FILTER (
        VALUES ( 'Access DB Adviser Rostered Days'[Start] ),
        'Access DB Adviser Rostered Days'[Start] <= MAX ( Dates[Date] )
    )
)

Previous Rostered Days = 

    CALCULATE (
        [NSW Previous Working Days],
        FILTER (
            VALUES ( 'Access DB Adviser Rostered Days'[Start] ),
            'Access DB Adviser Rostered Days'[Start] <= MAX ( Dates[Date] )
        ),
        FILTER (
            VALUES ( 'Access DB Adviser Rostered Days'[End] ),
            'Access DB Adviser Rostered Days'[End] >= MIN ( Dates[Date] )
        )
    )

 NSW Previous Working Days =
CALCULATE (
    [NSW Business Days],
    FILTER (
        VALUES ( Dates[Day Of Week Name] ),
        COUNTROWS (
            FILTER (
                'Access DB Adviser Rostered Days',
                Dates[Day Of Week Name]
                    = 'Access DB Adviser Rostered Days'[Rostered Day Of Week]
                    && 'Access DB Adviser Rostered Days'[Adviser Working Day] = TRUE ()
                    && NOT ( ISBLANK ( 'Access DB Adviser Rostered Days'[End] ) )
            )
        )
    )
)

NSW Current Working Days =
CALCULATE (
    [NSW Business Days],
    FILTER (
        VALUES ( Dates[Day Of Week Name] ),
        COUNTROWS (
            FILTER (
                'Access DB Adviser Rostered Days',
                Dates[Day Of Week Name]
                    = 'Access DB Adviser Rostered Days'[Rostered Day Of Week]
                    && 'Access DB Adviser Rostered Days'[Adviser Working Day] = TRUE ()
                    && ISBLANK ( 'Access DB Adviser Rostered Days'[End] )
            )
        )
    )
)

image

6

Ok some complexity here that makes it hard to grasp everything, especially without reviewing the model or testing.

With totals, here’s what I recommend, and how I think about things.

Really think about what those formula are doing without any context.

For example
FILTER (
VALUES ( Dates[Day Of Week Name] ),

This part of the formula is iterating through the days in a week, but is not being filtered by any month anymore. So be conscious of this.

Let’s also have a look at the first filter

VALUES ( ‘Access DB Adviser Rostered Days’[Start] ),
’Access DB Adviser Rostered Days’[Start] <= MAX ( Dates[Date] )

Here in the total section your are iterating through every single ‘start’ recorded and evaluating if that is less than the very last date in the date table.

MAX ( Dates[Date] ) - remember this is evaluating the MAX date of every date in the date table (unless you have some other filter I can’t see

Once you’ve filter out dates you don’t need (eventhough it looks like in the total you’re not filter out any dates, because MAX date is evaluating the very last date) you then move into NSW current working days and then evaluate that logic with no context at all.

Is this making sense.

These aren’t always easy I know

7

Hi @adsa,

I had a somewhat similar issue a while ago, here is what I came up with in case it helps: Date of status shift

One change we made in our data that helped is inserting a dummy date of 12/31/9999 for records that were active (i.e., truly didn’t have an end date). This way every record had a start and end date to work with.

8

@sam.mckay yes it does makes sense. i will go through the measures again and try and work it out

@swenzel thank you for your suggestion. you have just sparked an idea that i will try and work out over the weekend.

if i run into any issues i’ll post them back.