Using Date Range Slicer in measure.

[UmairKhankhail]

Hi All!

I am new to powerbi community. I am searching it since last month and found it very userful. Hope, I get my problem solved. I am creating powerbi report for a payroll analyzer. These are my master and transaction data columns and data model.

Master Data:

Transaction Data:

Data Model:

I have created a report which is as follows:

In this above bar graphs, I want to have a legends of employees which continue, leavers,joiners and leavers & joiners with in a dynamic date range selected through the date slicer.

Now, Since we can not use slicer date within a calculated columns, so I want anyone could help me creating measures for employees which continue,leavers,joiners and leavers & joiners within in a dynamic date range selected through slicers... We have given employee starting and leaving date within date... The leaving date column could have blank value but the starting date would always be given.

Thanks!

[amitchandak]

@UmairKhankhail , Please HR blog and measures, if those can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[UmairKhankhail]

Actually when I use  measures: joiners,leavers,joiners & leavers and continue, in the field parameter. The slicer build through field parameter does not affect the charts on the report.

[Anonymous]

Hi @UmairKhankhail ，

I'm not clear about your requirement. Do you want to get the count of active employees during selected date period? Or you want to create a calculated column with the values(continue,leavers,joiners and leavers & joiners) and put it on the bar chart as legend? Could you please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[UmairKhankhail]

Yes, exactly I want calculated column with the values(continue,leavers,joiners and leavers & joiners) so that I could put it on the bar chart as legend. But I think we can not use dynamic dates coming from slicer in the calculated column (becuase when we change date range in sclier the values of continue,leavers,joiners and leavers & joiners should also change), so we will have to use measure instead of column i think.... if we had a way to use it in a calculated column then it would be much better.

The sample data and .pbix file is as follows:

https://drive.google.com/drive/folders/1jFczcMPTL1TUmAtTJW-YFz3m5_v4zWg9?usp=sharing 

[Anonymous]

Hi @UmairKhankhail ,

The value of a calculated column is computed during data refresh and uses the current row as a context, it will not change according to the user interaction(slicer, filter, column selections etc.) in the report. So it can't achieve your requirement(change the column value(continue,leavers,joiners and leavers & joiners) base on slicer selections) by creating a calculated column. Please review the following links about the difference of calculated column and measure...

Calculated Columns and Measures in DAX

Calculated Columns vs Measures

We can consider creating a measure and put this new measure onto the Tooltips option. Could you please provide the conditions of how to get these values(continue,leavers,joiners and leavers & joiners)? For example: Continue, when it can be set as Continue? Thank you.

Best Regards