Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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 par...
Need Help
Anonymous's avatar
Anonymous
Not applicable
8 years ago

Anonymous try adding the first date calculation in the hires table. 

 

Is the transaction date hooked to the date table? 

what is the calculation for the first date?

Is first date driven by a filter?

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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!