Forum Discussion
Calculated column based on filters.
I want to create the following measure:
Hires = count of those employees whose transaon date = date[first date]). When I manually enter the first date, the value comes up.
The Manual part is the date column. However, when I give it as follows: I get an error that CALCULATE has been used in a TRUE/FALSE expression which is wrong, so I tried the one as below
But this just show a blank value for me even when I apply filters. However, for manually entering the date it comes up. Basically I would like to calculate the count of employees, if the filter the criteria that Employees[transaction_date)=Date(First_date). The first date has been created in a new table by me and works perfectly when filters are applied and changes accordingle. However, when I apply the same filter to the calculation, it does not work.
Any suggestions? Thank You!
17 Replies
My first table is Employees:
ID Transaction Date
1 7/31/2018
2 7/31/2018
3 7/31/2018
4 12/31/2017
5 12/31/2017
6 12/31/2017
7 12/31/2016
8 12/31/2016
9 12/31/2016
And a Date Table:
Date Year
(Ranges from 1/1/2010 to 12/31/2020) (The corresponding year)
I have added to measures in the Date Column as : First Date = FIRSTDATE('Date Table'[Date])-1
Last Date = IF(YEAR(LASTDATE('Date Table'[Date]))=YEAR(TODAY()), LASTDATE(Sheet1[TRANSACTION_DATE]), LASTDATE('Date Table'[Date]))
So the First date is actually 12/31/year of whatever year is chosen. Ex if year filter is 2013, first date should show 12/31/2012 and
last date is December of that year.
However, if my year is 2018, then last date needs to the last date of July that is 7/31/2018. And similarly next month, it would automaticlly change tp 8/31/2018.
The date table and empoyees table are linked by transaction date in the employee table and date in the date table. The filter used is the year column.
I need to calculate Hiring Rate as HR = count of hires/((COunt of employees on 12/31/2016 + count of employees on 12/31/2017))/2 if the year chosen is 2017.
Similarly, HR in 2018 eould be count of hires/((Count if employees on 12/31/2017 + Count of employees on 7/31/2017))/2 if year is 2018. The 7/31/2018 in denominator would automatically change to 8/31/2018 in August.
This is what i am trying to do and the denominator part is confusing me.
For the first part of denominaot, I gave HR1 = CALCULATE(COUNT(Employees[ID), FILTER(Employees, Employees[TRANSACTION_DATE]=Date Table[First DAte])) -> Syntax was correct.
Seconf part of denominaotr, I gave HR2 = CALCULATE(COUNT(Employees[ID]), FILTER(Employees, Employees[TRANSACTION_DATE]='Date Table'[Last Date])).
So the filters need to apply accordingly and work, is what i thought.
I hope i was able to explain the situation appropriately!