Efforts Allocation Weekly

[Anonymous]

Hi,

I have the following sample data with me, 

Efforts	Start_date	Due_date
5	1/1/2020	1/7/2020
10	1/1/2020	1/8/2020
15	1/1/2020	1/16/2020
20	1/1/2020	1/14/2020

Efforts - Represents quantified work that needs to be done 

Start_date: Gives the date when that work starts off 

Due_date: gives the date by within the work needs to be completed. 

Now, I need to split this efforts weekly like below on PowerBI (Expectation is to derive those columns represented in BOLD and its values) 

Efforts	Start_date	Due_Date	NoOfWeeks	Week Number1	Week Number 2	Week Number 3
5	1/1/2020	1/6/2020	1	5
10	1/1/2020	1/7/2020	1	10
15	1/1/2020	1/8/2020	2	7.5	7.5
20	1/1/2020	1/14/2020	2	10	10
9	1/1/2020	1/16/2020	3	3	3	3

 Calculation of Week is based on the logic of (7 days representing one week) 

Week Number1	January 1 - January 7
Week Number 2	January 8 - January 14
Week Number 3	January 15 - January 21

By taking few assistance over the community, there was an attempt made but it is resulting upon Error: 

Some data to experiment and worked out PBIX file is here : https://1drv.ms/u/s!AmnSr2uYQLNPcwP8_U-R-mJV-K8?e=kzDne3 

The splitted efforts in week number are giving me error, if you could see the PBIX i have shared. The logic written may seem to fail because of the data i have over cross years. 

My data has 2019,2020 and 2021 efforts list. Could this be a reason? If so can someone help me fixing it? 

If there is any other alternate approach can be shared will also be applicable. 

Note: The data shared on (Sample_data_1.csv) can have data of 2019, 2020, 2021. But currently what I am looking for is to have the efforts splitted for the entries that falls in 2020. (Week 1 - Week 53 of the year 2020) . 

Thanks,

Govind

[v-eachen-msft]

Hi @Anonymous ,

Your fnWeeknumber needs a date type parameter, but your two date columns are date/time type.

And you could use IF() to return 2020/01/01 or 2020/12/31 if data is out of the calendar range.

[Anonymous]

@v-eachen-msft :  Would you mind explaining the IF () function to a more detailed level? I am not really sure where this needs to be plugged in. 

Thanks,

Govind