Forum Discussion
Marking Dates In Between Two Dates (for Displaying Time Spread in days of an activity)
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
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)
7 Replies
johnt75Super User
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
Its counting for all dates
speedrampsSorry 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)
- 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. 😀
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
