Using COUNTX to Identify how many times a date in a date dimension table lies between time periods

[JamesLindsay101]

Hi!

I am currently trying to find out how many times in a date table, each date lands within a table of absences.

I have a date dimension table, pretty standard, and the absence table includes a start and end date of each absence.

I would like to put together a COUNT formula of some kind which counts each time a date in the date dimension table lands in between (inclusive) of each of the start and end dates in the absence table.

I've currently got the below:

Absences is my absence table and Sickness Days Date Dimension is my date dimension table.

Total Sickness Days by Day = CALCULATE(COUNTX(Absences,filter(CROSSJOIN(Absences,'Sickness Days Date Dimension'),'Sickness Days Date Dimension'[Date] >= Absences[Absence Start Date] && 'Sickness Days Date Dimension'[Date]<= Absences[Absence End Date])))

I am getting the below error which I assume comes from the CROSSJOIN function but if I don't inhclude that I can't filter using both tables.

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

any help?

Thanks in advance.

Note I can't share data as it is confidential data.

[v-xiaotang]

Hi @JamesLindsay101 

Thanks for reaching out to us.

>> I have a date dimension table, and the absence table includes a start and end date of each absence.

please share some sample data and the  expected output, thanks!

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[JamesLindsay101]

Hi,

My date dimension table is a usual date by date (DD/MM/YYYY) table, which also includes a calculated column identifying if the date is a weekday or a weekend.

My absence table a column for absence start dates, a column for absence end dates, and columns containing information regarding type of absence, and information on the employee who's absence it is. The quantity of days absent is automatically calculated by the HR information system.

below is a small example of some rows of data...

Employee Status

Employee Number

First Name

Last Name

Start Date

Termination Date

Employee Type

Rank

Division (Label)

Absence Type

Absence Start Date

Absence End Date

Quantity of Days

Active

101

First1

Last1

01/01/2022

N/A

Permanent

Associate

Marketing

Illness

30/06/2022

05/07/2022

4

Active

102

First2

Last2

01/11/2021

N/A

Fixed Term Worker

Associate

Sales

Illness

04/07/2022

10/07/2022

5

What I'm aiming for is a calculated column in my date dimension table which calculates how many times each date in my date dimension column is in between all the absences. For example, in the date dimension table where the date 04/07/2022 is, based on the example data above the calculated column should count 2, but for the date 30/06/2022 it should count 1.

Let me know if you need any further information from me, thanks in advance.

Edit:

I've also tried the below formula in a calculated column but this is erroring out ('USERELATIONSHIP function can only be used in the CALCULATE function'),

Total Sickness Days by Day = CALCULATE(COUNTROWS(Absences),USERELATIONSHIP('Sickness Days Date Dimension'[Date],Absences[Absence Start Date]) && RELATED('Sickness Days Date Dimension'[Date]) >= Absences[Absence Start Date],USERELATIONSHIP('Sickness Days Date Dimension'[Date],Absences[Absence End Date]) && RELATED('Sickness Days Date Dimension'[Date]) <= Absences[Absence End Date])

[daXtreme]

@JamesLindsay101 

Just create a table in which you'll store each and every day for any of the absences (meaning, between an absence's start and end date). Then you can even create a connection between your date dimension table and the new one. Once you've got this, it'll be dead easy to calculate anything because your calendar will be filtering the new table (call it ExpandedAbsences, should be hidden).

For instance, say you select one date from your date dim and want to know how many absences there were on this very day. This is the measure that does it:

[# Absences] = distinctcount( ExpandedAbsences[AbsenceId] ).  // AbsenceId links ExpandedAbsences to Absences.

How simple is that, huh?

[JamesLindsay101]

Thanks for getting back to me, appreciate your speediness!

Just to clarify in your initial paragraph, so for each absence, have as many rows as there are days absent in each absence event?

Do you know if there's an easy way to create this? in my current absence table I have 17,000 rows of absence events.