Forum Discussion
Helper IDax formula or Data transformation based on date columns
I have a dataset in Excel like this:
I'm trying to create a month-end headcount report in Power BI like this:
I'm wondering if I must:
1. keep the data as-is and use DAX to create the visualizations
(or)
2. transform the data like below, which is pretty straight-forward for reporting
Please advise which is the best path, and what's the best approach to build the logic.
The data is fine as it is, you don't need to transform it. Make sure that your date table is not connected to the employee table, and you can create a headcount measure like
Headcount = VAR MinDate = MIN ( 'Date'[Date] ) VAR MaxDate = MAX ( 'Date'[Date] ) VAR Result = CALCULATE ( DISTINCTCOUNT ( 'Employee'[Employee ID] ), 'Employee'[Start Date] <= MinDate && ( ISBLANK ( 'Employee'[End Date] ) || 'Employee'[End Date] > MaxDate ) ) RETURN Result
4 Replies
johnt75Super User
The data is fine as it is, you don't need to transform it. Make sure that your date table is not connected to the employee table, and you can create a headcount measure like
Headcount = VAR MinDate = MIN ( 'Date'[Date] ) VAR MaxDate = MAX ( 'Date'[Date] ) VAR Result = CALCULATE ( DISTINCTCOUNT ( 'Employee'[Employee ID] ), 'Employee'[Start Date] <= MinDate && ( ISBLANK ( 'Employee'[End Date] ) || 'Employee'[End Date] > MaxDate ) ) RETURN Result
v-veshwara-msftCommunity Support
Hi AnalyticsFreak ,
Thanks for posting in Microsoft Fabric Community and thanks johnt75 for responding and sharing the measure.
AnalyticsFreak , just wanted to check if the response provided was helpful. If further assistance is needed, please reach out.
Thank you.- v-veshwara-msft
Hi AnalyticsFreak ,
Just checking in to see if you query is resolved and if any responses were helpful.
Otherwise, feel free to reach out for further assistance.Thank you.
- v-veshwara-msft
Hi AnalyticsFreak ,
We wanted to kindly follow up regarding your query. If you need any further assistance, please reach out.
Thank you.