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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
purpur
Frequent Visitor

DAX, measure, date on report between start and end dates

Hello all

 

I am relatively new with DAX. I have tried to pick my brains for quite some time now with DAX measure; rather unsuccesfully though.

 

Data in question is HR data. Each employment contract has start date and end date as shown in picture below.

data.png 

 

Both dates have a relationship to calendar table, also shown below (just a part of the actual model which has several more tables).

relationships.png

 

I would like to know how I can write a measure

  1. which gives me number of employees at chosen date
  2. which gives me number of employees during chosen month or year (even a day within chosen time period should mean the person is counted in the results

 

I have tried creating a measure for personnel amount at chosen date with and without calendar relationships. I have tried using DAX formula

CALCULATE(COUNT('Contract'[DimTyontekija_Id]);filter('Contract';'Contract'[Start Date] <= max('Calendar'[Date]) || 'Contract'[End Date] >= min('Calendar'[Date])))

but changing the date gives blank result. I suppose inactive relationships are the cause for this, but as far as I understand I can't use USERELATIONSHIP in filter. Or the culprit might be entirely something else. 

 

What comes to the second measure I would like to create - I have not even started banging my head at my table yet.

 

Any and all help would be greatly appreciated.

- mira

14 REPLIES 14

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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