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

[purpur]

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.

 

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

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

[YingyiChen]

Hi,

For anyone who is still looking for an anwer on this quetion. I came accross the pefect answer here:

It's auhor is Sergio Murru. Thank you so much Sergio!!

https://www.pbiusergroup.com/communities/community-home/digestviewer/viewthread?MessageKey=9ef57e03-...

[htp84]

I am a bit late to the party but I cannot see any solution that fulfills the requirements that OP states. You should wrap your MIN and MAX with a CALCULATE function. 

CALCULATE(COUNT('Contract'[DimTyontekija_Id]);
FILTER('Contract';'Contract'[Start Date] <= CALCULATE(MAX('Calendar'[Date])) && 
'Contract'[End Date] >= CALCULATE(MIN('Calendar'[Date]))))

See the following homepage: https://forum.powerpivotpro.com/forums/topic/dax-to-sum-values-between-start-and-end-date-and-use-sl...

All credit to tomallan

[Anonymous]

Have you tried using COUNTA() or COUNTROWS() instead of COUNT()?

[purpur]

Counting rows is not a solution since a person can have 1..n work contracts, even simultaneously.

[v-jiascu-msft]

Hi @purpur,

Try this measure out please.

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Contract'[Employee] ),
    FILTER (
        'Contract',
        'Contract'[Start Date] <= MAX ( 'Calendar'[Date] )
            && 'Contract'[End Date] >= MIN ( 'Calendar'[Date] )
    )
)

Best Regards,

Dale

[purpur]

@v-jiascu-msft - Thank you for your suggestion.

The difference with the measure I have tried was DISTINCTCOUNT vs COUNT and && vs ||. You are correct, I should have use DISTINCTCOUNT. What comes to && vs ||, the difference in results should not be that big. My original or-operator should have been more ... lenient. 

While the relationships between contract and date tables are in place in the data model, and the measure shows blank in my Power BI test report when I choose a date.

[Anonymous]

It kind of rings me a bell... next thing I would try is FIRSTDATE() and LASTDATE() instead of min and max.

Check as well if the column type for the dates in each table is exactly the same as. A similar thing happened to me when one column was date only and the other date & time, and it only found a match where the time stamp was 00:00:00.

[purpur]

@Anonymous, @v-jiascu-msft - Thank you for speedy reaction.

I tried suggestion @Anonymous hinted about, but no wanted results. Measure still shows as blank when choosing a date.

I have tried 

Test:=CALCULATE(DISTINCTCOUNT('Contract'[DimTyontekija_Id]);filter('Contract';'Contract'[Start Date] <= lastdate('Calendar'[Date]) && 'Contract'[End Date] >= FIRSTDATE('Calendar'[Date])))

Test:=CALCULATE(DISTINCTCOUNT('Contract'[DimTyontekija_Id]);filter('Contract';'Contract'[Start Date] <= lastdate('Calendar'[Date]) || 'Contract'[End Date] >= FIRSTDATE('Calendar'[Date])))

as well as earlier with

Test:=CALCULATE(DISTINCTCOUNT('Contract'[DimTyontekija_Id]);filter('Contract';'Contract'[Start Date] <= max('Calendar'[Date]) && 'Contract'[End Date] >= min('Calendar'[Date])))

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

The dates in date table and in contract table are in same format.

I must be overlooking something. ...Right?

- mira

[purpur]

Has no one really needed to model this [1..n start and end dates, possibly simultaneous ones] before? No one ever faced this scenario before? I would have assumed this would actually be quite typical situation.

I would greatly appreciate help on this. Anyone, please?

- mira

[Anonymous]

@purpurYour issue was really intriguing me, so I decided to give it a try. I'm new as well to DAX and Power BI, but the apparently simple logic challenged me. Your hint about "I suppose inactive relationships are the cause for this..." put me on the right track. As you cannot have two active relationships involving the same two tables, you will need two calendars.

I set up a simple model with just one table 'Contracts' with the columns [Contract ID], [Start] and [End]

Then I created two identical calendars, CalendarStart and CalendarEnd using the min and max valuess of the contract start and contract end date columns:

CalendarStart =
CALENDAR (
    MIN ( FIRSTDATE ( 'Contracts'[End] ); FIRSTDATE ( 'Contracts'[Start] ) );
    MAX ( LASTDATE ( 'Contracts'[End] ); LASTDATE ( 'Contracts'[Start] ) )
)
CalendarEnd =
CALENDAR (
    MIN ( FIRSTDATE ( 'Contracts'[End] ); FIRSTDATE ( 'Contracts'[Start] ) );
    MAX ( LASTDATE ( 'Contracts'[End] ); LASTDATE ( 'Contracts'[Start] ) )
)

I setup the relationships from CalenderEnd[Date] to Contracts[End] and from CalendarStart[Date] to Contracts[Start].

Then I created the measure Headcount:

Headcount =
CALCULATE (
    DISTINCTCOUNT ( Contracts[Contract ID] );
    FILTER (
        'Contracts';
        'Contracts'[Start] <= LASTDATE ( CalendarEnd[Date] )
            && 'Contracts'[End] >= FIRSTDATE ( CalendarStart[Date] )
    )
)

Finally I added two slicers, and here it gets confusing:

The slicer on CalendarEnd (relative, After) is used to select the start date of your reporting period.

The slicer on CalendarStart (relative, Before) is used to select the end date of your reporting period.

This will actually give you both measures

a) any employee who has started either before or within the period or has ended either within or after the period.

b) the number of active employees on a given day (simply set start date and end date to the same date).

It will even give you a 3rd measure. This may not be desired, but I couldn't figure out how to suppress the option:

c) if you set the End Date BEFORE the Start Date in the slicers, it will exclude any employees who have either started or ended within that selected inverse period, ie. they started before the first day AND ended after the last day of the selected period.

See some sample results below:

Between Dec 12, 2017 and Jan 12, 2018, there were 19 active employees.

Of these 19 employees, 4 have neither started nor ended within the (inverse) period, which means that 15 have either started and/or ended within that same period:

Dec 12 - Jan 12

Inversed dates

Let me know if this solves your issue.

[purpur]

@Anonymous Huh, no idea! I have it as a mail. Need it? 

On more serious note, I have not had the chance to test your solution yet. It would, however, benefit others as well to be able to see your suggestion anyway.

[purpur]

@Anonymoussorry for not being able to get back to this any sooner. I tested your solution but was unable to verify it worked correctly. In my opinion it did not provide correct results, and I was supposed to create a generic sample data to prove it. However, I have been swamped - as we all, thus not being able to do s

I ended up tearing our data apart and creating data to date level in all occasions where start date-end dates were used. Good thing in it is that it removes the complexity of slicers in reports, and on the other side it increases the number of rows - a lot, from hundreds of thousands to tens of millions. Oh well..

I will get back to this later though, should someone else still need this kind of technique. So your effort was not in vain! It was very much appreciated.

[Anonymous]

@purpur

Your issue was really intriguing me, so I decided to give it a try. I'm new as well to DAX and Power BI, but the apparently simple logic challenged me. Your hint about "I suppose inactive relationships are the cause for this..." put me on the right track. As you cannot have two active relationships involving the same two tables, you will need two calendars.

I set up a simple model with just one table 'Contracts' with the columns [Contract ID], [Start] and [End]

Then I created two identical calendars, CalendarStart and CalendarEnd using the min and max valuess of the contract start and contract end date columns:

CalendarStart =
CALENDAR (
    MIN ( FIRSTDATE ( 'Contracts'[End] ); FIRSTDATE ( 'Contracts'[Start] ) );
    MAX ( LASTDATE ( 'Contracts'[End] ); LASTDATE ( 'Contracts'[Start] ) )
)

CalendarEnd =
CALENDAR (
    MIN ( FIRSTDATE ( 'Contracts'[End] ); FIRSTDATE ( 'Contracts'[Start] ) );
    MAX ( LASTDATE ( 'Contracts'[End] ); LASTDATE ( 'Contracts'[Start] ) )
)

I setup the relationships from CalenderEnd[Date] to Contracts[End] and from CalendarStart[Date] to Contracts[Start].

Then I created the measure Headcount:

Headcount =
CALCULATE (
    DISTINCTCOUNT ( Contracts[Contract ID] );
    FILTER (
        'Contracts';
        'Contracts'[Start] <= LASTDATE ( CalendarEnd[Date] )
            && 'Contracts'[End] >= FIRSTDATE ( CalendarStart[Date] )
    )
)

Finally I added two slicers, and here it gets confusing:

The slicer on CalendarEnd (relative, After) is used to select the start date of your reporting period.

The slicer on CalendarStart (relative, Before) is used to select the end date of your reporting period.

This will actually give you both measures

a) any employee who has started either before or within the period and has ended either within or after the period.

b) the number of active employees on a given day (simply set start date and end date to the same date).

It will even give you a 3rd measure. This may not be desired, but I couldn't figure out how to suppress the option:

c) if you set the End Date BEFORE the Start Date in the slicers, it will exclude any employees who have either started or ended within that selected inverse period, ie. they started before AND ended after the selected period.

See some sample results below:

Between Dec 12, 2017 and Jan 12, 2018, there were 19 active employees.

Of these 19 employees, 4 have neither started nor ended within the (inverse) period, which means that 15 have either started and/or ended within that same period

 From Dec 12 to Jan 12

Inversed dates

Please let me know if this solves your issue.

[v-jiascu-msft]

Hi @Anonymous,

You are right. 2018-01-01 and 2018-01-01 13:33:10 are two different values. The || (or) would give all the values. You can try it.

Best Regards,

Dale