Calculation using user selection/input

[Anonymous]

Hi, 

I am looking for some advice on how to deal with the following scenario. The requirement is as follows:

• The user has to select one of the dates from a drop-down (and not slicer; for a yyyy-mmm format).
• If the user selects "January 2020", the count (new calc.column) should be 2 (as two employees have joined in/before Jan 2020)
• Similarly, the count should be 3 for "February 2020" and 4 for "March 2020".

Employee	Joining Date
A	January 2020
B	March 2020
C	November 2019
D	February 2020

Could you please suggest a solution for this? I have used Spotfire quite extensively before, and would have used property controls, but am unable to get my head around a solution in PBI.

Thanks,

Vivek

[amitchandak · Accepted solution]

@Anonymous , If joining date is a date, like this you can do by selecting a month

Use date calendar table and join it with Joining date

measure =
var _month =maxx(Date,date[Month])
var _max = maxx(filter(Date,date[month]=_month),Date[date])
return
calculate(count(Table[Employee]),Filter(all(Date),Date[date]<=_max))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

[Anonymous]

Thank you very much sir!