Spread a value over a finish and start date

[SajBZ]

Hi, 

I have the data table below as a sample. 

I want some help on how to spread the "Costum" value on this table on every day of the days from start to finish day. 

I already have a separate calendar table as well so if the formula contains a separate calendar table, it should be all fine.

The table below show an example of what I need to achieve out of this. 

Name	date start	date finish	duration	costum		12/10/2024	13/10/2024	14/10/2024	15/10/2024	16/10/2024	17/10/2024
a	12/10/2024	17/10/2024	5	0.16666		0.16666	0.16666	0.16666	0.16666	0.16666	0.16666

Also if I can exclude weekends from this calculation it'll be really great. 

Any advice would be greatly appreaciated. 

Thank you

[_AAndrade]

Hi @saud968,

I'm attaching a pbix file with my solution.

[audreygerred · Accepted solution]

Hi! You can create a custome column in Power Query that will generate of list of dates between your start and finish, then you can expand that to rows. 

Here is the M-Code I used to make this work:

List.Dates([start date], Duration.Days([finish date] - [start date]) + 1, #duration(1, 0, 0, 0))

I started with some sample data:

Then, in Power Query, I went to Add Column, then chose Custom Column and used the code I have pasted above.

Find the new column that is added:

Click the arrows at the top of this column

Click Expand to New Rows: 

Then, I removed the start and finish date column, but you don't have to. Here is what I wind up with:

[SajZadeh]

Thank you I think this is working for what I need, What if you want to exclude the weekends from this list ? 

[audreygerred]

I would suggest having a date table (SQLBI has a great one) DAX Date Template - SQLBI

If there isn't a field that designates weekday or weekend you can use the day of week field and filter out Saturday and Sunday in your reporting. If you want the rows to not even be in your dataset, you can add a column in Power Query that will designate the day of the week then remove Saturday and Sunday from there.

[saud968]

reate a Measure for Daily Costum:
Daily Costum =
VAR StartDate = SELECTEDVALUE(MainTable[date start])
VAR EndDate = SELECTEDVALUE(MainTable[date finish])
VAR TotalDays = COUNTROWS(
FILTER(
Calendar,
Calendar[Date] >= StartDate &&
Calendar[Date] <= EndDate &&
WEEKDAY(Calendar[Date], 2) < 6 -- Exclude weekends
)
)
RETURN
IF(
Calendar[Date] >= StartDate &&
Calendar[Date] <= EndDate &&
WEEKDAY(Calendar[Date], 2) < 6, -- Exclude weekends
DIVIDE(SELECTEDVALUE(MainTable[costum]), TotalDays, 0),
BLANK()
)

Create a Matrix Visual:
Add a Matrix visual to your report.
Add Name to Rows.
Add Calendar[Date] to Columns.
Add the Daily Costum measure to Values.
Explanation
VAR StartDate and EndDate: These variables store the start and finish dates for each row.
VAR TotalDays: This variable calculates the total number of days between the start and finish dates, excluding weekends.
IF Statement: This checks if the current date in the Calendar table is within the start and finish dates and is not a weekend. If true, it calculates the daily Costum value by dividing the total Costum by the number of valid days.

Best Regards
Saud Ansari
If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!