Calendar Date filter not working as expected, how to resolve this issue

[omkarsawant30]

Context -> I have 2 Tables - HR Talk Forms and User. HR Talk Forms contain one row each for every HR Talk taken place between Employee and HR POC. User table consists of data of all active employees working in the company. 

Requirement -> Create 100% stacked bar charts to analyse the completion % of HR talk forms by different fields like Department, Country, etc. User should be able to filter Year (Eg. 2023 or 2024) and Half year (Eg. H1 or H2) to see the results.

Situation - > Since HR Talk Forms table contains data of only completed HR talks, its date field cannot be used to filter data for the requirement, so i created a Calendar table, so that i can have a date field , but when i apply it, it shows 100% for completions for all Departments or Countries, which is incorrect. 

Please see the attached data model and chart designed. Kindly help me on this issue.

[amitchandak · Accepted solution]

@omkarsawant30 , You need a table to give total employees by department/date and use that measure to create a % measure and use that in clustered bar visual

[omkarsawant30]

Thanks @amitchandak ! Based on the reply , i created a new table by mapping all dates of year 2023 and 2024 with all Employee ID, department, country and Talent coordinator. The solution has worked, using distinct count of Employee ID in the charts.

DateEmployeeMapping =

CROSSJOIN(
    SUMMARIZE(
        FILTER('User', 'User'[EmployeeStatus] = "Active"),
        'User'[UserId], 'User'[Department], 'User'[TalentCoordinator], 'User'[Country]
    ),
    GENERATESERIES(DATE(2023, 1, 1), DATE(2024, 12, 31), 1)
)

[v-shex-msft]

Hi @omkarsawant30 ,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[omkarsawant30]

@v-shex-msft Sure but i dont see any option in the reply section to attach excel files. Kindly guide.