Marking Dates In Between Two Dates (for Displaying Time Spread in days of an activity)

[San_Raz]

I have two tables 
Table 1 : WorkSchedule

	Activity	Date		Expected Finish		Client
	A	01/01/2025		04/01/2025		SS
	A	01/01/2025		3/01/2025		DD
	C	05/01/2025		8/01/2025		SS

And table two is a Date Table.

I have connected the date column in date table to the Date column in WorkSchedule table  using a One to many relationship 
with Workschedule lying in the many side.

I need to create matrix table with the dates and activities listed down as below. the dates in-between the Date and ExpectedFinish to be counted as 1.

Actity

Client

01/01/2025

02/01/2025

03/01/2025

04/01/2025

05/01/2025

06/01/2025

07/01/2025

08/01/2025

09/01/2025

10/01/2025

11/01/2025

12/01/2025

A

SS

1

1

1

1

A

DD

1

1

1

C

SS

1

1

1

1

Also, i have been trying the beow dax but it is only  counting  the start dates with 1
Count_Working = COUNTX
                                           (FILTER(WorkSchedule,
                                                          WorkSchedule[Date] <= MAX(Date[Date]) &&
                                                           WorkSchedule[EXPECTED FINISH] >= MAX(Date[Date])),
                                                           WorkSchedule[Activity])
Let me know if any other info is required

[speedramps]

Try this ....

Remove the Calendar relationship.

)Calendar relations should not be used forstart to finish date sceranrios because the rleationshiop will only work on one date and not the dates inbetween)

Create measure (using you own table and column names)

Answer = 
VAR mydate = SELECTEDVALUE('Calendar'[Date])
VAR tempfile = 
FILTER(yourdata, 
mydate >= yourdata[Start date] &&
mydate <= yourdata[Finish date]
)
RETURN
COUNTROWS(yourdata)

Add a matric visual to your reports

Please click the [accept solution] and thumbs up button.

At the very least click the thumbs up button to show your appreciation.

Thank you. 😀

[San_Raz]

Hi,
In your A -SS ends on 05th , but in your result it is counted for days after that also.
I also tested your approach, but it is giving Wrong output

[johnt75]

I think you can use

Date is active =
VAR StartDate =
    SELECTEDVALUE ( WorkScheduleWorkSchedule[Date] )
VAR EndDate =
    SELECTEDVALUE ( WorkSchedule[Expected Finish] )
VAR CurrentDate =
    SELECTEDVALUE ( 'Date'[Date] )
VAR Result =
    IF ( StartDate <= CurrentDate && CurrentDate <= EndDate, 1 )
RETURN
    Result

[San_Raz]

Its counting for all dates

[speedramps · Accepted solution]

Sorry for tytping mistake.

I have corrrected it

Answer = 
VAR mydate = SELECTEDVALUE('Calendar'[Date])
VAR tempfile = 
FILTER(yourdata, 
mydate >= yourdata[Start date] &&
mydate <= yourdata[Finish date]
)
RETURN
COUNTROWS(tempfile)

[San_Raz]

Perfect, Thanks.
I have a doubt, without relation between the two tables, how is the matrix visual able  determine the relation between the coulmn and Rows.

Thanks in Advance

[speedramps]

I hace added some explanatory commnents the DAX measure to help answer your question "how is the matrix visual able  determine the relation between the coulmn and Rows without relation between the two tables."

When Power Bi plots a matrix it does the DAX calculation for each cell which will have natural filter context for the rows and columns.

Please click [accept solution] and thumbs up buttons. Thank you

Answer = 
// get the date for the column context on the visual 
VAR mydate = SELECTEDVALUE('Calendar'[Date])

// create a temp file of yourdate 
// which will naturally be filtered by the row Activity and Customer contect in the visual
// but we add additional date filters
VAR tempfile = 
FILTER(yourdata, 
mydate >= yourdata[Start date] &&
mydate <= yourdata[Finish date]
)
RETURN
// count the rows in the temp file for the visual cell context 
// which has Activity and Customer row context and Date column context
COUNTROWS(tempfile)