Requirements: because the domestic legal holiday, not 6 class fills and holiday, etc., due to everyone again in different time the number of days is also different, I need reference induction time to calculate by today's actual working days everyone,
Reference 2020 holiday arrangement, so I will all need to work to make a list,
Table 1: need to work day (only in January, for example)

date weekOn Thursday, 2020/1/2

2020/1/3 Friday
2020/1/6 Monday2020/1/7 Tuesday
On Wednesday, 2020/1/8
On Thursday, 2020/1/9

2020/1/10 Friday
2020/1/13 Monday2020/1/14 Tuesday
On Wednesday, 2020/1/15
On Thursday, 2020/1/16

2020/1/17 Friday2020/1/19 Sunday

2020/1/20 Monday2020/1/21 Tuesday
On Wednesday, 2020/1/22
On Thursday, 2020/1/23

2020/1/31 Friday

Table 2: personnel information
Induction time name
Zhang SAN 2019/12/12
Li si 2020/1/15

The result of the need to:
In powerpivot calculated fields "working days" are used to calculate the actual value

The following is my own understanding of written expression (express intent only)
Set today for the 2020-1-20
The actual working days=count (table 1 (week), table 1 [date} & gt; induction time []=table 2 and table 1 [date] <=today ())

The final result needs to be:
Zhang SAN 14 (including today)
Li si 5 (because of late, so not before, and today is the day, don't go to work, 18, 19, is the domestic class)

Please expert advice, thank you,

CodePudding user response:

Ask for help, thank you.

CodePudding user response:

This method of calculation of the actual number of days in the DAX many, for example:

RiBiao method 1: to the work of the date and the staff induction time contrast, select eligible date, then calculate number of rows

. The actual work days _countrows_filter=
COUNTROWS (
The FILTER (
'work RiBiao' 
AND (' work RiBiao [date] & gt;=SELECTEDVALUE (' personnel information] [induction time, MIN (' work RiBiao '[date])),' work RiBiao [date] <=TODAY ()) 
) 
) 

method 2: the overall train of thought and method 1 is about the same, just to work RiBiao date compared with staff induction time, meet the sum after converted to 1

. The actual work days _sumx=
SUMX (
'work RiBiao' 
AND (' work RiBiao [date] & gt;=SELECTEDVALUE (' personnel information] [induction time, MIN (' work RiBiao '[date])),' work RiBiao [date] <* 1=TODAY ()) 
) 

method 3: the staff induction time to today's date to build a date table, and work RiBiao do intersection, and then calculate the intersection of line number, the general method is as follows:

. The actual work days _countrows_intersect=
COUNTROWS (
Intersects (
Values (' work RiBiao [date]), 
CALENDAR (
SELECTEDVALUE (' personnel information [] induction time, min (' work RiBiao '[date])), 
TODAY () 
) 
) 
)