Forum Discussion
date comparison
Hi All,
Trying to create the report, when i search for student id it will compare a prior week period total cost from Project start & a week after project finish.
example
if i select Student 1 will compare ( project start 12/20/20 - WK 51)
will sum cost from 12/13/20 to 12-19/20 - WK50
and (project finish 1/2/2021 - WK1)
will sum cost from 1/3/21 to 1/9/21 -WK2
thanks in advance
Table 1
| Student | Project start | Project Finish |
| Student 1 | 12/20/2020 | 1/2/2021 |
| Student 2 | 1/4/2020 | 1/11/2021 |
Table 2
| Date | day | week | student | Cost |
| 12/13/2020 | Sun | 51 | Student 1 | 1,024 |
| 12/14/2020 | Mon | 51 | Student 1 | 1,257 |
| 12/15/2020 | Tue | 51 | Student 1 | 1,357 |
| 12/16/2020 | Wed | 51 | Student 1 | 1,328 |
| 12/17/2020 | Thu | 51 | Student 1 | 1,280 |
| 12/18/2020 | Fri | 51 | Student 1 | 1,380 |
| 12/19/2020 | Sat | 51 | Student 1 | 1,133 |
| 12/20/2020 | Sun | 52 | Student 1 | 1,036 |
| 12/21/2020 | Mon | 52 | Student 1 | 1,481 |
| 12/22/2020 | Tue | 52 | Student 1 | 1,402 |
| 12/23/2020 | Wed | 52 | Student 1 | 1,540 |
| 12/24/2020 | Thu | 52 | Student 1 | 1,350 |
| 12/25/2020 | Fri | 52 | Student 1 | 1,068 |
| 12/26/2020 | Sat | 52 | Student 1 | 958 |
| 12/27/2020 | Sun | 53 | Student 1 | 1,007 |
| 12/28/2020 | Mon | 53 | Student 1 | 1,378 |
| 12/29/2020 | Tue | 53 | Student 1 | 1,298 |
| 12/30/2020 | Wed | 53 | Student 1 | 1,533 |
| 12/31/2020 | Thu | 53 | Student 1 | 1,338 |
| 01/01/2021 | Fri | 1 | Student 1 | 1,239 |
| 01/02/2021 | Sat | 1 | Student 1 | 1,087 |
| 01/03/2021 | Sun | 2 | Student 1 | 1,080 |
| 01/04/2021 | Mon | 2 | Student 1 | 1,369 |
| 01/05/2021 | Tue | 2 | Student 1 | 1,531 |
| 01/06/2021 | Wed | 2 | Student 1 | 1,324 |
| 01/07/2021 | Thu | 2 | Student 1 | 1,313 |
| 01/08/2021 | Fri | 2 | Student 1 | 1,450 |
| 01/09/2021 | Sat | 2 | Student 1 | 888 |
Thanks so much for the help. This also will give the solution. But got to many data to use calculated column.
7 Replies
Thansk Ashish for your help!!!
You are welcome.
Ashish,
Noticed with this formula it will start calculating cost day after the finish date + seven days.
Next week cost = CALCULATE([Total cost],DATESBETWEEN('Calendar'[Date],[Finish date]+1,[Finish date]+7))is there a way to to calculate the a week after finish date. say for example belowfinish date falls under week 2. cost calculation should start from Week 3.Also i i only want to calculate working days in that week what will be the formula.date wk
1/3/2021 2 finish date
1/4/2021 2
1/5/2021 2
1/6/2021 2
1/7/2021 2
1/8/2021 2
1/9/2021 2
1/10/2021 3 start calculation from here
1/11/2021 3
1/12/2021 3
1/13/2021 3
1/14/2021 3
1/15/2021 3
1/16/2021 3 end here as next week costapologize kinda new in pbi. Appreciate the help.Hi,
In the Calendar table, you will have to give me 2 additional columns - Week number and Isworkday (with a Yes/No)?
nardtmopbix attached
https://drive.google.com/file/d/1OLeszOikKkGTl9SeCYNB8HCDq0HgkqGX/view?usp=sharing
create a calculated columns in table 1
[table 1 (renamed as master), table 2 (renamed as data)]
startuqid = IF(LOOKUPVALUE(data[week],data[student],master[Student],data[Date],master[Project start])=0,0, LOOKUPVALUE(data[week],data[student],master[Student],data[Date],master[Project start])-1) finishuqid = IF(LOOKUPVALUE(data[week],data[student],master[Student],data[Date],master[Project Finish])=0,0,LOOKUPVALUE(data[week],data[student],master[Student],data[Date],master[Project Finish])+1)then the following measures
finishtotal:= SUMX(FILTER(data,data[week]=MAX(master[finishuqid])),[Cost]) starttotal:= SUMX(FILTER(data,data[week]=MAX(master[startuqid])),[Cost])no relationship required
Thanks so much for the help. This also will give the solution. But got to many data to use calculated column.
