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

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

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

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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!