Measure to ignore min date slicer but not max date

[lloydthomas2399]

I have the following measure created which is to get the date of the last shift worked by employee:

Date of last shift = CALCULATE ( MAX ( 'Staffing Calendar'[CalendarDate] ), ALL ( 'Staffing Calendar' ), 'Fact Staffing Plan' ).

I have a date slicer on the dashboard.

Currently, I have a data table with a sum of the shifts worked per employee within the date slicer range, and I want a column for the date of their last shift as per my measure. This works great and shows the date of their last shift within the date range selected. However, I would like it to show the date of their last shift if it is out of range of the minimum date range selection, i.e. if my date range was 1st April 2023-31st Septemeber 2023 and an employees last shift date was 20th Jan, I would want this to show as thier last shift date even though my date slicer is out of range for this. Currently, their last shift date would be blank.

I would still need to account for the max date range selection as for example, if my date range is set to today, I wouldn't want to see someone's last date shift as a future date becasue they are scheduled in for a shift, i.e. if they are down for a shift in December 2023 I don't want to see this if my date range is up to October 2023.

Could anyone recomment any changes to my measure?

Thanks

[lloydthomas2399]

Hi @v-rongtiep-msft & @TomasAndersson  I'm still stuck on this and wondering if you guys could help me, I've created a PBIX file but have no option to upload it. Could you recommend how to attach the PBIX file so you can see some sample data that matches my data model set up.

As you would see in the PBIX, if you set the slicer date to just the 1st october 2023, only Person 4 shows as they did a shift on that date:

If you set the slicer to 1st oct-30th Oct, Persons 1-4 all show with a 'last date' in that period:

What i would want is on the first scenario above, if only the 1st Oct is selected, the other employees 'last date' would show for prior to october, therefore ignoring the min date range. If you were to go back, these people do have a shift recorded priot to 1st Oct so i would like to show that date even though it is out of the slicer range.

Hope this makes sense and helps.

Expected output could be (made up):

Slicer date range 01/10/2023-01/10/2023
Person 1 last date - 20/06/2023

Person 2 last date - 05/09/2023

Person 3 last date - 10/08/2023

Peron 4 last date - 01/10/2023

[v-rongtiep-msft]

Hi @lloydthomas2399 ,

One possible solution to your problem is to use the ALLSELECTED function instead of the ALL function in your measure. The ALLSELECTED function removes all filters from the entire filter context except for filters that have been applied to the specified columns or tables. This means that your measure will respect the filters applied by the date slicer, but will ignore any other filters that might affect the date column.

For example, you can use this formula:

Date of last shift = 
CALCULATE (
    MAX ( 'Staffing Calendar'[CalendarDate] ),
    ALLSELECTED ( 'Staffing Calendar' ),
    'Fact Staffing Plan'
)

This will ensure that you always get the date of the last shift for each employee, even if it is out of range of the minimum date range selection. However, it will also respect the maximum date range selection, so you will not see any future dates.

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

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

[lloydthomas2399]

Hello @v-rongtiep-msft , thank you for the reply and apologies for the late response, this does not solve my issue unfortunately. It gives the same output as my measure.

[TomasAndersson]

Hi!
I'm not sure how your data is structured, what is used to filter dates for what and so on, but you could try and see if any of these work. Basically you need to somehow separate what the date range you can pick from and the shift dates for the employees.

Date of last shift := 
CALCULATE( 
    MAX('Fact Staffing Plan'[DateColumnFromThisTable]), 
    FILTER(ALL (Staffing Calendar), 'Staffing Calendar'[CalendarDate] <= MAX('Staffing Calendar'[CalendarDate])
).

Date of last shift := 
VAR __MaxDate = MAX('Staffing Calendar'[CalendarDate])
return
CALCULATE( 
    MAX('Staffing Calendar'[CalendarDate]), 
    FILTER(ALL ('Staffing Calendar'), 'Staffing Calendar'[CalendarDate] <= __MaxDate,
    'Fact Staffing Plan'
)

[lloydthomas2399]

Thank you for the reply @TomasAndersson  but unfortuantely the output is no different.