cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

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 IDJoin DateEffective DateCount/Do not Count
11-Apr-2030-Jun-20count
22-Apr-2030-Jun-20count
33-Apr-2030-Jun-20count
41-May-2030-Jun-20count
52-May-2030-Jun-20count
63-May-2030-Jun-20count
730-Jun-2030-Jun-20count
81-Jul-2030-Jun-20do not count
931-Mar-2030-Jun-20do not count
101-Apr-2031-May-20do not count
112-Apr-2031-May-20do not count
123-Apr-2031-May-20do not count
131-May-2031-May-20do not count
142-May-2031-May-20do not count
153-May-2031-May-20do not count
1630-Jun-2031-May-20do not count
171-Jul-2031-May-20do not count
1831-Mar-2031-May-20do 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!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi, @daniel092031 

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.

View solution in original post

9 REPLIES 9
Super User IV
Super User IV

@daniel092031 , 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

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@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!

Microsoft
Microsoft

Hi, @daniel092031 

 

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
        )
    )

v-janeyg-msft_0-1603696789593.png

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, @daniel092031 

 

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.

@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...

Hi, @daniel092031 

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.

View solution in original post

@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? 

Hi, @daniel092031 

 

Try this:

Measure 2 =
VAR b =
    SELECTEDVALUE ( 'Table'[Effective Date] )
RETURN
    SWITCH (
        TRUE (),
        MONTH ( b ) < 7,
            COUNTROWS (
                FILTER (
                    ALL ( 'Table' ),
                    [Join Date]
                        >= DATE ( YEAR ( b ) - 1, 7, 1 )
                        && [Join Date] <= b
                )
            ),
        COUNTROWS (
            FILTER (
                ALL ( 'Table' ),
                [Join Date] >= DATE ( YEAR ( b ), 7, 1 )
                    && [Join Date] <= b
            )
        )
    )

Best Regards

Janey Guo

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors