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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
thisguy
Advocate I
Advocate I

display only the last n months of a rolling multi year total while still being able to filter

I need to be able to show the last 6 months headcount of the full org and be able to filter by department. I am having trouble working out the correct DAX.

 

in the measure below If I use ALL then when a slicer is selected it will not filter by department, if i use ALLSELECTED it does not show the correct count which only shows the total of the last 6 months (filter on visual) starts - finishes.

 

I have the following FACT_Data table structure: 

EMPID   Start/Finish     Date       DEPT

0322Employment Date7/03/20201
0366Employment Date19/04/20211
1043Employment Date7/03/20201
1044Employment Date1/03/20222
1177Employment Date11/02/20222
1232Employment Date13/05/20212
1239Employment Date27/04/20201
1239Termination Date10/07/20201
1369Employment Date4/01/20212
1369Termination Date26/02/20212

 

I have a measure that gives a rolling headcount: 

Employee Count =
VAR started =
CALCULATE (
COUNT ( 'FACT_Data'[Start/Finish] ),
'FACT_Data'[Start/Finish] = "Employment Date",
FILTER (
ALLSELECTED ( 'FACT_Data' ),
'FACT_Data'[Date] <= MAX ( DIM_Calendar[Date] )
),all(DIM_Calendar[Date])
)
VAR ended =
CALCULATE (
COUNT ( 'FACT_ Data'[Start/Finish] ),
'FACT_Data'[Start/Finish] = "Termination Date",
FILTER (
ALLSELECTED ( 'FACT_Data' ),
'FACT_ Data'[Date] <= MAX ( DIM_Calendar[Date] )
),all(DIM_Calendar[Date])
)

RETURN
started-ended

 

 

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @thisguy 
The following seems to work fine 

Employee Count = 
VAR CurrentDate = MAX ( Dim_Calendar[Date] )
VAR SelectedData = ALLSELECTED ( FACT_Data )
VAR EmpData = FILTER ( SelectedData, FACT_Data[Start/Finish] = "Employment Date" )
VAR TerData = FILTER ( SelectedData, FACT_Data[Start/Finish] = "Termination Date" )
VAR Employed = COUNTROWS ( FILTER ( EmpData, FACT_Data[ Date ] <= CurrentDate ) )
VAR Terminated = COUNTROWS ( FILTER ( TerData, FACT_Data[ Date ] <= CurrentDate ) )
RETURN
    Employed - Terminated

2.png1.png

View solution in original post

2 REPLIES 2
tamerj1
Super User
Super User

Hi @thisguy 
The following seems to work fine 

Employee Count = 
VAR CurrentDate = MAX ( Dim_Calendar[Date] )
VAR SelectedData = ALLSELECTED ( FACT_Data )
VAR EmpData = FILTER ( SelectedData, FACT_Data[Start/Finish] = "Employment Date" )
VAR TerData = FILTER ( SelectedData, FACT_Data[Start/Finish] = "Termination Date" )
VAR Employed = COUNTROWS ( FILTER ( EmpData, FACT_Data[ Date ] <= CurrentDate ) )
VAR Terminated = COUNTROWS ( FILTER ( TerData, FACT_Data[ Date ] <= CurrentDate ) )
RETURN
    Employed - Terminated

2.png1.png

OMG!   amazing thankyou so much!!!

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors