Dynamical categories and count of rows by categories on date

[JānisB]

I have a DateTable and a fact table Table1:

EmployeeID | StartDate | EndDate

Employee1 | 2018-03-18 | 2025-04-25

Employee2 | 2019-04-20 | null

...

The result should look like:

0-5, 6-10, 11-15, ... are groups of work experience (full years). If work experience is 5,9, then 5 full years.

The amounts are counts of employees (actives).

It is necessary to filter those data on date, for example on 2025-03-31 (could be year and month).

How to make this vizualisation? How to make dynamical count of employees by dynamical groups of work experience?

For example:

Employee1 - on 2025-03-31 should be in group 6-10, but on 2025-04-30 is not in the report because EndDate 2025-04-25

Employee2 - on 2025-03-31 should be in group 0-5, but on 2025-04-30 should be in group 6-10

[v-sgandrathi]

Hi @JānisB,

Has your issue been resolved?If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.

If yes, kindly accept the useful reply as a solution and give us Kudos. It would be appreciated.

Thank you for your understanding!

[techies · Accepted solution]

Hi @JānisB you can try this by creating a disconnected table for experience groups (e.g., 0–5, 6–10, etc.). Then use the following measure to count employees in each group, based on the selected reporting date

Employees in Bucket =

VAR SelectedDate = MAX('Date'[Date])

VAR MinYOS = MIN('Experience Groups'[MinYears])

VAR MaxYOS = MAX('Experience Groups'[MaxYears])

RETURN

CALCULATE (

    COUNTROWS (Table2),

    FILTER (

        Table2,

        Table2[StartDate] <= SelectedDate &&

        (ISBLANK(Table2[EndDate]) || Table2[EndDate] >= SelectedDate) &&

        VAR YOS = DATEDIFF(Table2[StartDate], SelectedDate, YEAR)

        RETURN YOS >= MinYOS && YOS <= MaxYOS

    )

)

[bhanu_gautam]

@JānisB 

If you don't already have a Date Table, you can create one usingDAX
DateTable =
ADDCOLUMNS (
CALENDAR (DATE(2018, 1, 1), DATE(2025, 12, 31)),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"YearMonth", FORMAT([Date], "YYYY-MM")
) 

Add two calculated columns to your Table1 to calculate the work experience in full years as of a given date.

StartYear = YEAR([StartDate])
EndYear = IF(ISBLANK([EndDate]), YEAR(TODAY()), YEAR([EndDate]))

Create a measure to calculate the work experience in full years as of the selected date.

WorkExperience =
VAR SelectedDate = MAX(DateTable[Date])
VAR EndDate = IF(ISBLANK(SELECTEDVALUE(Table1[EndDate])), SelectedDate, SELECTEDVALUE(Table1[EndDate]))
VAR WorkExperienceYears = DATEDIFF(SELECTEDVALUE(Table1[StartDate]), EndDate, YEAR)
RETURN WorkExperienceYears

Create a measure to count the number of employees in each work experience group.

EmployeeCountByExperienceGroup =
VAR SelectedDate = MAX(DateTable[Date])
RETURN
SUMX (
VALUES(Table1[EmployeeID]),
VAR WorkExperienceYears =
DATEDIFF(
Table1[StartDate],
IF(ISBLANK(Table1[EndDate]), SelectedDate, Table1[EndDate]),
YEAR
)
RETURN
SWITCH(
TRUE(),
WorkExperienceYears >= 0 && WorkExperienceYears <= 5, 1,
WorkExperienceYears > 5 && WorkExperienceYears <= 10, 2,
WorkExperienceYears > 10 && WorkExperienceYears <= 15, 3,
WorkExperienceYears > 15 && WorkExperienceYears <= 20, 4,
WorkExperienceYears > 20 && WorkExperienceYears <= 25, 5,
WorkExperienceYears > 25 && WorkExperienceYears <= 28, 6,
0
)
)

Add a slicer to your report to select the date.
Create a table or matrix visualization.
Add the work experience groups and the measure EmployeeCountByExperienceGroup to the visualization.