DAX Measure calculating values for each day within a period

[Anthony67]

Require assitance please:

FactTable

Course	Delegate	Start date	End date
a123	a	2020/01/06	2020/01/10
a123	b	2020/01/06	2020/01/10
b234	f	2020/01/07	2020/01/07
b234	y	2020/01/07	2020/01/07
b567	z	2020/01/07	2020/01/10

DimDate is other table 

Looking for this solution:

Course	2020/01/06	2020/01/07	2020/01/08	2020/01/09	2020/01/10	Total
a123	2	2	2	2	2	10
b234		2				2
b234		1	1	1	1	4
Total	2	5	3	3	3	16

Must be able to cover period where course extends over more than one week, month etc

In this case the measure must exclude weekends.

[lbendlin]

Use the standard CROSSFILTER(...,..., none) approach to fill the gaps.

[Anthony67]

Thanks for the promt response.

I could do with more help in terms of this soulution.

I am relatively new to powerbi and need more detail.

[lbendlin]

If you are new to Power BI this might be a bit of a steep learning curve.  Anyway, here's the first step of the solution.  

Create an inactive relationship between the end date  and the date in the DimDate table.

The result will calculate the individual items properly.  For the Totals you will need different calculations.

[lbendlin]

Here is an alternative version that allows you to keep the table relationships active and that gets the column totals right. Still needs work for the row totals. Bonus - added weekend filter too.

[lbendlin]

@Anthony67   One more version. Now the row and column totals are correct but the overall total is not yet.  No idea how to change that...

Delegates2 = 
var da= SELECTEDVALUE(DimDate[date])
var de= CALCULATE(count(FactTable[Delegate]),allexcept(FactTable,FactTable[Course]),FactTable[Start date]<=da, da<=FactTable[End date])
var me= sumx(filter(DimDate,DimDate[date]>=min(FactTable[Start date]) && DimDate[date]<=max(FactTable[End date]) ),count(FactTable[Delegate]))
return if(HASONEVALUE(DimDate[date]),de,me)

I also don't know what happens if people start the same course at different times. 

[lbendlin]

ok, last version, promise!  I didn't get the grand total right but I managed to make the wrong one disappear. Good enough for me.

Delegates2 = 
var da= SELECTEDVALUE(DimDate[date])
var sd= SELECTEDVALUE(FactTable[Start date])
var ed= SELECTEDVALUE(FactTable[End date])
var de= CALCULATE(count(FactTable[Delegate]),allexcept(FactTable,FactTable[Course]),FactTable[Start date]<=da, da<=FactTable[End date])
var me= sumx(filter(DimDate,DimDate[date]>=sd && DimDate[date]<=ed ),count(FactTable[Delegate]))
return if(HASONEVALUE(DimDate[date]),de,me)

[lbendlin]

Promises schmomises.  Here's the complete version including correct grand total.  I am pretty sure this can be optimized.

Delegates = 
var da= SELECTEDVALUE(DimDate[date])
var sd= SELECTEDVALUE(FactTable[Start date])
var ed= SELECTEDVALUE(FactTable[End date])
var de= CALCULATE(count(FactTable[Delegate]),allexcept(FactTable,FactTable[Course]),FactTable[Start date]<=da, da<=FactTable[End date])
var me= sumx(filter(DimDate,DimDate[date]>=sd && DimDate[date]<=ed ),count(FactTable[Delegate]))
var tt=ADDCOLUMNS(values(DimDate[date]),"ct",CALCULATE(count(FactTable[Delegate]),Filter(FactTable,FactTable[Start date]<=[date] && [date]<=FactTable[End date])))
return if(HASONEVALUE(DimDate[date]),de,if(HASONEVALUE(FactTable[Course]),me,sumx(tt,[ct])))

[Anthony67]

I am blown away by the effort you are making to assist me!

😀I dont mind you breaking your promises.
The solution is streats ahead of where I was but is still not returning the result I am looking for.

I am confident that it is something small or somthing I am miss applying.