Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 "...
Need Help
Tutorial Requests
  • v-janeyg-msft's avatar
    v-janeyg-msft
    5 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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!