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

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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        v-janeyg-msft Thanks a lot! It definitely helps!!!! 

         

        May I ask you further (just ignore my question if you do) ?

        I need to create a similar measure for the year. For example:

        Selected Jun 2020: Calculate from Jul 2019 to Jun 2020

        Selected May 2020: Caculate from Jul 2019 to May, and so on...

         

        Any better way other than mine that shown to you previously?