date comparison

[nardtmo]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may download my solution file from here.

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my solution file from here.

Hope this helps.

[nardtmo]

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 below

finish 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 cost

apologize kinda new in pbi. Appreciate the help.

[Ashish_Mathur]

Hi,

In the Calendar table, you will have to give me 2 additional columns - Week number and Isworkday (with a Yes/No)?

[nardtmo]

Thansk Ashish for your help!!!

[Ashish_Mathur]

You are welcome.

[smpa01]

@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

[nardtmo · Accepted solution]

Thanks so much for the help. This also will give the solution. But got to many data to use calculated column.