Calculated column based on filters.

[Anonymous]

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!

[Anonymous]

@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]

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!

[Anonymous]

Hey, how do I post the Power BI File?

[Anonymous]

Post it to dropbox or google drive and send a link here.

[Anonymous]

@Anonymous

Same FileExcel -https://drive.google.com/open?id=1rNEwU3Wz9PF2m7BtzgTjVcOd9JIjsS-_

PB File -https://drive.google.com/open?id=18zU64MwydFlv2ZlSVAA0ZTzJfwcLpTiH

[Anonymous]

@Anonymous Here are somethings I found. If you plug transaction date, date and first date in one table you will realize that first date dynamically changes based on what you prop in to 1 day prior to dec 31, 2016. 

To fix this I removed -1 from first date formula 

Also

1) first date & Last date need to be calculated columns. 

2) I created a new related column in sheet1 related(date[first date])

Also all the dates need to be in same format either datetime or date.

[Anonymous]

@Anonymouswere you able to calculate the denominator accordinglt? If yes, can u share the file with me?

[Anonymous]

@Anonymous if you apply the same changes I made to firstdate to last date it should work properly. the formula is the same just the fields are different.

[Anonymous]

okay i'll try and let u know in a while! Thanks @Anonymous@

[Anonymous]

@Anonymous was my solution the accepted solution???

[Anonymous]

@Anonymous Hey, Sorry. I just tried it and it dint work. However, I have usd tweaking to make it work now.

[v-jiascu-msft]

Hi @Anonymous,

Could you please share the solution and mark it as a solution?

Best Regards,

Dale

[Anonymous]

Hey I have the following situation.

I have given a date range from 2010 to 2020.

I want to create the following measure: Count the number of employees if date hired of employees = last date in date table (I created this date table), else 0.

I created the following measure till now:  Measure = CALCULATE(COUNT(Employees[ID]), Filter(Employees, Employees[HireDate]= 'Date Table'[Last Date])

So bascally I want to calculate the count when hire date matches last date, else I want it to return 0. However, it returns weird values even for the year 2019 in the filter. Is there a way in which this can be calculated?

Thank You!

[v-yuezhe-msft]

@Anonymous,

What is the last date in date table? Is it a measure? Could you please share sample data of your table and post expected result  based on the sample data?

Regards,
Lydia

[Anonymous]

Hey, I had posted my sample under a different discussion called "Calculated Column based on Filters" yesterday. There is a detailed description there. @v-yuezhe-msft

[v-yuezhe-msft]

@Anonymous，

Do you want to get same expected result in the two different discussion? If so, I will merge the  two threads.

Regards,
Lydia

[Anonymous]

@v-yuezhe-msft Yes, They are related. So I guess you can merge it. Thank You!