Split data across week

[raimon]

Hi All, 

I am struggling with one calculation. I have weekly data mapped to Sundays.

The date contains data of the prior week. 

Data- 

Scheme	Start Date	End Date
Scheme1	5-Jul-20	2-Aug-20
Scheme2	5-Jul-20	19-Jul-20
Scheme3	12-Jul-20	9-Aug-20

If a scheme is running for 2 weeks, for both weeks there should be a count. 

Example Output - 

Week Date	Scheme1	Scheme2	Scheme3	Total
5-Jul-20	1	1		2
12-Jul-20	1	1	1	3
19-Jul-20	1	1	1	3
26-Jul-20	1		1	2
2-Aug-20	1		1	2
9-Aug-20	1			1

Please help with how do I calculate so that I can get proper count.

Also note I have some data which has start and end date an year apart. 

[v-yalanwu-msft · Accepted solution]

Hi, @raimon ;

You could create a date table about every sunday, then create a measure to calculate the count like below:

1.create a date table.

Date = FILTER( ADDCOLUMNS( CALENDAR(DATE(2020,7,1),DATE(2020,9,1)),"weekday",WEEKDAY([Date],2)),[weekday]=7)

2.create a measure.

Measure = 
var _value=CALCULATE(COUNT('Table'[Scheme]),FILTER(ALL('Table'),[Scheme]=MAX('Table'[Scheme])&&[Start Date]<=MAX('Date'[Date])&&[End Date]>=MAX('Date'[Date])))
return IF(ISFILTERED('Table'[Scheme]),_value,CALCULATE(COUNT('Table'[Scheme]),FILTER(ALL('Table'),[Start Date]<=MAX('Date'[Date])&&[End Date]>=MAX('Date'[Date]))))

then use a matrix  and the final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HotChilli]

Disconnected date table with a measure like :

MeasureT = VAR _dt = MAX(DatesTable[Date])
RETURN
COUNTROWS(FILTER(TableG, TableG[Start Date] <= _dt && TableG[End Date] >= _dt))

Put the date field in the rows of a matrix, scheme in the columns and measure in the Values.

You might need to edit the conditions in the matrix to suit (<,<= etc).

Filter the dates table to get the rows you need (Sundays, I think).

Also Scheme 1 and 3 look wrong in the desired table.