topic Re: Marking Dates In Between Two Dates (for Displaying Time Spread in days of an activity) in DAX Commands and Tips

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

San_Raz — Wed, 15 Jan 2025 09:40:59 GMT

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

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

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

johnt75 — Wed, 15 Jan 2025 09:59:38 GMT

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

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

speedramps — Wed, 15 Jan 2025 10:31:29 GMT

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. 😀

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

San_Raz — Wed, 15 Jan 2025 10:07:54 GMT

Its counting for all dates

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

San_Raz — Wed, 15 Jan 2025 10:29:28 GMT

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

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

speedramps — Wed, 15 Jan 2025 11:11:26 GMT

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)

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

San_Raz — Wed, 15 Jan 2025 10:41:06 GMT

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

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

speedramps — Wed, 15 Jan 2025 11:22:29 GMT

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)