Forum Discussion
filter another date column based on selected date
Hi,
I am developing a HR dashboard. I have appended a set of monthly data, user can select date (related to data's effective date) by using silcer to see the data as of the month. When user select "Jun 2020" then the dashboard would show only data as of Jun 2020.
Here are the senarios.
Selected date = 30 Jun 2020 (= Fiscal Year 20 Q4)
I want to count number of new joiner in Q4, so I need to get worker whose join date is between 1 April to 30 Jun.
So It totally based on user selected date and filter another column (JoinDate).
You may take below table as a sample data.
Desire outcome is 7.
| Staff ID | Join Date | Effective Date | Count/Do not Count |
| 1 | 1-Apr-20 | 30-Jun-20 | count |
| 2 | 2-Apr-20 | 30-Jun-20 | count |
| 3 | 3-Apr-20 | 30-Jun-20 | count |
| 4 | 1-May-20 | 30-Jun-20 | count |
| 5 | 2-May-20 | 30-Jun-20 | count |
| 6 | 3-May-20 | 30-Jun-20 | count |
| 7 | 30-Jun-20 | 30-Jun-20 | count |
| 8 | 1-Jul-20 | 30-Jun-20 | do not count |
| 9 | 31-Mar-20 | 30-Jun-20 | do not count |
| 10 | 1-Apr-20 | 31-May-20 | do not count |
| 11 | 2-Apr-20 | 31-May-20 | do not count |
| 12 | 3-Apr-20 | 31-May-20 | do not count |
| 13 | 1-May-20 | 31-May-20 | do not count |
| 14 | 2-May-20 | 31-May-20 | do not count |
| 15 | 3-May-20 | 31-May-20 | do not count |
| 16 | 30-Jun-20 | 31-May-20 | do not count |
| 17 | 1-Jul-20 | 31-May-20 | do not count |
| 18 | 31-Mar-20 | 31-May-20 | do not count |
I have a calendar table with fiscal year, fiscal Quarter, etc. And "Date" column has related with "Effective Date" of the fact table.
I would like to create similar measure for FYTD, FYTD -1, FY QTD -1. As long as the above problem has been solved, these measures should easily be replicated.
Thx for your help!
Hi, Anonymous
Try this:Measure = var b=SELECTEDVALUE('Table'[Effective Date]) return SWITCH(TRUE(), MONTH(b) in{1,2,3},COUNTROWS(FILTER(all('Table'),MONTH([Join Date])>=1&&[Join Date]<=b)), MONTH(b) in{4,5,6},COUNTROWS(FILTER(all('Table'),MONTH([Join Date])>=4&&[Join Date]<=b)), MONTH(b) in{7,8,9},COUNTROWS(FILTER(all('Table'),MONTH([Join Date])>=7&&[Join Date]<=b)), COUNTROWS(FILTER(all('Table'),MONTH([Join Date])>=10&&[Join Date]<=b)) )If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
9 Replies
Anonymous , Create a slicer on an independent date table
and try a measure like
measure =
var _max =maxx(allselected(Date), Date[Date])
return
calculate([measure], filter(Table, Table[Join Date] <= _max && Table[Effective Date]>=_max))Also, refer my HR blog for HR apporach for both the dates
amitchandak Thanks for your reply.😁
First question, why do I need to create an idependent date table? Do you mean i have to disconnect the date table that I already had? Or create another one then create a slicer?
Second question is how do I make use of your solution and create a measure to get my desire period? I get you that you use Max function to return the selected date. But how can i get the start of fiscal year quarter (i.e. 1st April) from the reurned value? My calendar table exists column like fiscal year quarters, fiscal year, etc, can we make use of this?
Once again, thanks for your help!
Hi, Anonymous
It’s my pleasure to answer for you.
According to your description, I think you can create a measure to calculate the correct result.
Like this:
Measure = VAR a = SELECTEDVALUE ( 'Table'[Effective Date] ) RETURN COUNTROWS ( FILTER ( ALL ( 'Table' ), [Join Date] >= EOMONTH ( a, -3 ) + 1 && [Join Date] <= a ) )If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-janeyg-msft thanks for your reply! I think this can solve part of my problem.
If user select May 2020, can we make it returns calculation from April to May? It seems that your solution provided will return calculation from Mar to May 2020.
Thanks!
Hi, Anonymous
Do you want to calculate within a quarter of the selected date or something else? Please make it clear and unify(if select 6 calculate a quarter or calculate only one month before).
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
