cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
mochachiino
Frequent Visitor

Trouble filtering out data outside of date slicer

Hello All, I am stuck and cannot think how to further filter out my dax measure.

I have 2 tables, DateCalendar and v_Clients. The v_Clients has multiple columns of dates where I have set up the primary relationship between the DateCalendar[DateInt] and v_Clients[CertifyDate]. To clarify all the date columns in v_Clients are in integer format, yyyymmdd. For the measure I am creating, I am trying to find all eligible clients within a time frame set by my date slicer. To do this I am looking at 2 columns in the v_Clients table, [EligFromDate] and [EligToDate]. Everything works until I get towards the end. Data shows outside of my Date Slicer, for example if set to 2023, there will be data from 2022 or vice versa. The data is also incorrect and does not reflect proper data for the time period which in this picture is 2186.

mochachiino_0-1695705304921.png
I am assuming I could filter it out in the VAR Result statement, but I am not sure what else I can do. Any help would be greatly appreciated.

Here is sample data of v_Clients: In this instance of a date slicer of jan 2022- dec 2022, there should only be 2 clients who were eligible, #1 and #2.

IDCertifyDateEligFromDateEligToDate
1201910102019101220221012
2202210052022100720251007
3202212302023010120260101
4202301022023010520260105
 
Below is my measure:

ElgEligibleClients =
VAR MaxDate = VALUE(LEFT(MIN(DateCalendar[DateInt]),6))
VAR MinDate = VALUE(LEFT(MIN(DateCalendar[DateInt]),6))

VAR ElgClients =
CALCULATE(
COUNTROWS(v_Clients),
ALL(DateCalendar),
VALUE(LEFT(v_Clients[EligToDate],6))>=MinDate,
//2023-08 >= 2023-01
VALUE(LEFT(v_Clients[EligFromDate],6))<=MaxDate
//2020-08 <= 2023-12
)
VAR Result =
If(
MinDate<=VALUE(LEFT(MIN(v_Clients[EligFromDate]),6)),
//MaxDate<=VALUE(LEFT(MIN(DateCalendar[DateInt]),6)) && MinDate>=VALUE(LEFT(MIN(DateCalendar[DateInt]),6)),
ElgClients
)
RETURN
Result




 

 

 

 

 
1 ACCEPTED SOLUTION

Thanks for the materials. I found out the reason was due to bad source data and had to filter it out

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@mochachiino , seem very similar to HR, active employee measures

 

refer

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

 

 

 

Thanks for the materials. I found out the reason was due to bad source data and had to filter it out

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors