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.

v-janeyg-msft's avatar
v-janeyg-msft
Icon for Community Support rankCommunity Support
5 years ago

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.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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!

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      v-janeyg-msft Thanks,

       

      I would like to calculate within a quarter of the selected date.

       

      Selected Jun 2020: caculate from Apr to Jun

      Selected May 2020: caculate from Apr to May

      Selected Apr 2020: caculate within Apr

      Selected Mar 2020: calculate from Jan to Mar 2020, and so on

       

      Actually i have made a formula like this, but it seems stupid. I need to repulicate below logic to all 12 months. 

      if

       ( MONTH([Selected Date]) = 6, (CALCULATE(COUNTROWS('Data Source'),

       FILTER('Data Source','Data Source'[JoinDate] > EDATE([Selected Date],-3)),

       FILTER('Data Source','Data Source'[JoinDate] <= [Selected Date])))),
      if

       ( MONTH([Selected Date]) = 5, (CALCULATE(COUNTROWS('Data Source'),

       FILTER('Data Source','Data Source'[JoinDate] > EDATE([Selected Date],-2)),

       FILTER('Data Source','Data Source'[JoinDate] <= [Selected Date])))), and so on...
      • v-janeyg-msft's avatar
        v-janeyg-msft
        Icon for Community Support rankCommunity Support
        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.