Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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.

View solution in original post

9 REPLIES 9
v-janeyg-msft
Community Support
Community Support

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

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.

Anonymous
Not applicable

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

Anonymous
Not applicable

@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, @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
Not applicable

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

 

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

amitchandak
Super User
Super User

@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

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

 

 

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.