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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Slicer Filter Between Dates Measure to take first date and measure to take last date

I have an slicer with a between dates.

I have an employee table with Start Date and End Date (this could be blank when the employee is still working), it also has an area column and an ID for each employee

I want to have a table that will give me the following columns

 

Area / Employees at start of date / Employees that were dropped during that time / Employees that were hired during that time / Employees at the end of time

 

I'm trying to get the distinctcount ID with measures trying to use the min and max of the month date but can't make it work. I need help with each measure I should use for thoes 4 columns

1 ACCEPTED SOLUTION

Hi @Anonymous 

 

Try the measures below (you'll obviously have to swap out the columns references for your actual column names):

 

Employees at start of period = 
VAR MinDate = MIN ( Calendar[Date] )
VAR MaxDate = MAX ( Calendar[Date] )
VAR Result = 
CALCULATE ( 
    DISTINCTCOUNT ( EmployeeTable[ID] ),
    EmployeeTable[StartDate] < MinDate,
    OR ( EmployeeTable[EndDate] > MinDate, ISBLANK ( EmployeeTable[EndDate] ) )
)
RETURN
Result
Employees started in period = 
VAR MinDate = MIN ( Calendar[Date] )
VAR MaxDate = MAX ( Calendar[Date] )
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( EmployeeTable[ID] ),
        AND (
            EmployeeTable[StartDate] >= MinDate,
            EmployeeTable[StartDate] <= MaxDate 
        )
    )
RETURN
    Result
Employees dropped in period = 
VAR MinDate = MIN ( Calendar[Date] )
VAR MaxDate = MAX ( Calendar[Date] )
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( EmployeeTable[ID] ),
        AND (
            EmployeeTable[EndDate] >= MinDate,
            EmployeeTable[EndDate] <= MaxDate 
        )
    )
RETURN
    Result
Employees at end of period = 
VAR MinDate = MIN ( Calendar[Date] )
VAR MaxDate = MAX ( Calendar[Date] )
VAR Result = 
CALCULATE ( 
    DISTINCTCOUNT ( EmployeeTable[ID] ),
    EmployeeTable[StartDate] <= MaxDate,
    OR ( EmployeeTable[EndDate] >= MaxDate, ISBLANK ( EmployeeTable[EndDate] ) )
)
RETURN
Result

 

Hope this helps!

 

Best regards,

Martyn

View solution in original post

5 REPLIES 5
v-frfei-msft
Community Support
Community Support

Hi @Anonymous ,

 

Hope this bolg can help you.

https://radacad.com/from-and-to-date-slicers-in-power-bi-filtering-based-on-two-fields

 

Btw, kindly share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
MartynRamsden
Solution Sage
Solution Sage

Hi @Anonymous 

 

What column is driving the slicer? Is it coming from a date dimension table?

 

Best regards,

Martyn

Anonymous
Not applicable

Exactly is a date table not related to this table, also I try using an slicer coming from both the start and end date for the employee

Hi @Anonymous 

 

Try the measures below (you'll obviously have to swap out the columns references for your actual column names):

 

Employees at start of period = 
VAR MinDate = MIN ( Calendar[Date] )
VAR MaxDate = MAX ( Calendar[Date] )
VAR Result = 
CALCULATE ( 
    DISTINCTCOUNT ( EmployeeTable[ID] ),
    EmployeeTable[StartDate] < MinDate,
    OR ( EmployeeTable[EndDate] > MinDate, ISBLANK ( EmployeeTable[EndDate] ) )
)
RETURN
Result
Employees started in period = 
VAR MinDate = MIN ( Calendar[Date] )
VAR MaxDate = MAX ( Calendar[Date] )
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( EmployeeTable[ID] ),
        AND (
            EmployeeTable[StartDate] >= MinDate,
            EmployeeTable[StartDate] <= MaxDate 
        )
    )
RETURN
    Result
Employees dropped in period = 
VAR MinDate = MIN ( Calendar[Date] )
VAR MaxDate = MAX ( Calendar[Date] )
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( EmployeeTable[ID] ),
        AND (
            EmployeeTable[EndDate] >= MinDate,
            EmployeeTable[EndDate] <= MaxDate 
        )
    )
RETURN
    Result
Employees at end of period = 
VAR MinDate = MIN ( Calendar[Date] )
VAR MaxDate = MAX ( Calendar[Date] )
VAR Result = 
CALCULATE ( 
    DISTINCTCOUNT ( EmployeeTable[ID] ),
    EmployeeTable[StartDate] <= MaxDate,
    OR ( EmployeeTable[EndDate] >= MaxDate, ISBLANK ( EmployeeTable[EndDate] ) )
)
RETURN
Result

 

Hope this helps!

 

Best regards,

Martyn

Anonymous
Not applicable

Thank you very much, worked perfectly

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.