Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have the following two tables. The first table has ranges of dates which correspond to the shift dates of each person. Each shift date range is always continuous, for example, from 3 January 2022 to 7 January 2022, from 11 January 2022 to 15 January 2022, etc.
Work Shift Date | Name | Value | Submissions | Feedback |
03 January 2022 | Lizui | 1 | 4 | 2 |
04 January 2022 | Lizui | 1 | 7 | 5 |
05 January 2022 | Lizui | 1 | 8 | 8 |
06 January 2022 | Lizui | 1 | 5 | 6 |
07 January 2022 | Lizui | 1 | 7 | 5 |
11 January 2022 | Tegalpapak | 1 | 8 | 8 |
12 January 2022 | Tegalpapak | 1 | 5 | 6 |
13 January 2022 | Tegalpapak | 1 | 3 | 8 |
14 January 2022 | Tegalpapak | 1 | 3 | 3 |
15 January 2022 | Tegalpapak | 1 | 1 | 1 |
10 January 2022 | Lizui | 1 | 4 | 2 |
11 January 2022 | Lizui | 1 | 7 | 5 |
12 January 2022 | Lizui | 1 | 8 | 8 |
13 January 2022 | Lizui | 1 | 5 | 6 |
30 January 2022 | Frei Paulo | 1 | 6 | 5 |
31 January 2022 | Frei Paulo | 1 | 2 | 1 |
01 February 2022 | Frei Paulo | 1 | 3 | 4 |
02 February 2022 | Frei Paulo | 1 | 5 | 3 |
05 February 2022 | Gangarampur | 1 | 3 | 7 |
06 February 2022 | Gangarampur | 1 | 1 | 2 |
07 February 2022 | Gangarampur | 1 | 7 | 3 |
08 February 2022 | Gangarampur | 1 | 9 | 9 |
This the second table which shows the Reporting date, which is the date on which the person reported his shift location.
Reporting Date | Name | Location |
07 January 2022 | Lizui | Palembang |
14 January 2022 | Tegalpapak | Sapporo |
11 January 2022 | Lizui | Vijayawada |
31 January 2022 | Frei Paulo | Johannesburg |
07 February 2022 | Gangarampur | Chongqing |
The objective is to compare the Reporting date (from table 2) with the range of dates from the first table and if the Reporting date matches any of the dates, then the number of days in that date range is counted from table 1. The final results table is shown below. The table shows the name and the number of days spent per location per month, so if the shift period overlaps two months, then the number of days should be split between those two months, such as, for Frei Paulo.
Any help would be much appreciated!
Name | Year | Month | Number of days per location per month | Location |
Lizui | 2022 | January | 5 | Palembang |
Tegalpapak | 2022 | January | 5 | Sapporo |
Lizui | 2022 | January | 4 | Vijayawada |
Frei Paulo | 2022 | January | 2 | Johannesburg |
Frei Paulo | 2022 | February | 2 | Johannesburg |
Gangarampur | 2022 | February | 4 | Chongqing |
Solved! Go to Solution.
Hi @Anonymous ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Get the min date and max date for table1 group by Name and continuous dates
2. Create a measure as below to get the number of days which reporting days between min date and max date
Number of days per location per month =
VAR _year =
SELECTEDVALUE ( 'Date'[Year] )
VAR _month =
SELECTEDVALUE ( 'Date'[Month] )
RETURN
CALCULATE (
SUM ( 'Work Shift'[Value] ),
FILTER (
'Work Shift',
YEAR ( 'Work Shift'[Work Shift Date] ) = _year
&& 'Work Shift'[Work Shift Date].[Month] = _month
&& 'Work Shift'[Mindate] <= MIN ( 'Reporting'[Reporting Date] )
&& 'Work Shift'[Maxdate] >= MIN ( 'Reporting'[Reporting Date] )
)
)
Best Regards
Hi @Anonymous ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Get the min date and max date for table1 group by Name and continuous dates
2. Create a measure as below to get the number of days which reporting days between min date and max date
Number of days per location per month =
VAR _year =
SELECTEDVALUE ( 'Date'[Year] )
VAR _month =
SELECTEDVALUE ( 'Date'[Month] )
RETURN
CALCULATE (
SUM ( 'Work Shift'[Value] ),
FILTER (
'Work Shift',
YEAR ( 'Work Shift'[Work Shift Date] ) = _year
&& 'Work Shift'[Work Shift Date].[Month] = _month
&& 'Work Shift'[Mindate] <= MIN ( 'Reporting'[Reporting Date] )
&& 'Work Shift'[Maxdate] >= MIN ( 'Reporting'[Reporting Date] )
)
)
Best Regards
@Anonymous Maybe:
Number of Days Measure =
VAR __Name = MAX('Table2'[Name])
VAR __Date = MAX('Table2'[Reporting Date])
VAR __Table =
ADDCOLUMNS(
FILTER('Table1',[Name] = __Name),
"__Days",
VAR __WorkDate = [Work Shift Date]
VAR __Previous = MAXX(FILTER('Table1',[Name]=__Name) && [Work Shift Date] < __WorkDate)
RETURN
([Work Shift Date] - __Previous) * 1.
VAR __MinDate = MAXX(FILTER(__Table, [Work Shift Date] <= __Date && [__Days] <> 1),[Work Shift Date])
VAR __MaxDate = MINX(FILTER(__Table, [Work Shift Date] <= __Date && [__Days] <> 1),[Work Shift Date])
RETURN
(__MaxDate - __MinDate) * 1. + 1
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |