Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 ID | Join Date | Effective Date | Count/Do not Count |
1 | 1-Apr-20 | 30-Jun-20 | count |
2 | 2-Apr-20 | 30-Jun-20 | count |
3 | 3-Apr-20 | 30-Jun-20 | count |
4 | 1-May-20 | 30-Jun-20 | count |
5 | 2-May-20 | 30-Jun-20 | count |
6 | 3-May-20 | 30-Jun-20 | count |
7 | 30-Jun-20 | 30-Jun-20 | count |
8 | 1-Jul-20 | 30-Jun-20 | do not count |
9 | 31-Mar-20 | 30-Jun-20 | do not count |
10 | 1-Apr-20 | 31-May-20 | do not count |
11 | 2-Apr-20 | 31-May-20 | do not count |
12 | 3-Apr-20 | 31-May-20 | do not count |
13 | 1-May-20 | 31-May-20 | do not count |
14 | 2-May-20 | 31-May-20 | do not count |
15 | 3-May-20 | 31-May-20 | do not count |
16 | 30-Jun-20 | 31-May-20 | do not count |
17 | 1-Jul-20 | 31-May-20 | do not count |
18 | 31-Mar-20 | 31-May-20 | do 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!
Solved! Go to 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.
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.
@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.
@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.
@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
@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
@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!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |