Count a register if its between the start date and end date

[zohlander]

Hi! How are u? I was developing a DAX Measure and I got stucked.

I have a Calendar table and a fact table with a start date and a enddate. The calendar has an active relationship with the fact table by the column "start date", "enddate" is inactive.

I want to graph a bar chart with week as x axis and count the registers that had not ended yet.

For example: A register has a start date equal to 03/01/2024 (DD/MM/YYYY) and a end date equal to 23/01/2024. I have a month filter, if a select January I want to see this:

Week 1 : 1 (1/1/2024 - 7/1/2024) The register have been created
Week 2:  1 (8/1/2024 - 15/1/2024) Still open

Week 3:  1 (16/1/2024 - 23/1/2024) Still open

Week 4:    0 The register reached the end date

I want to graph this in a bar chart.

I would be very glad if you can help me! Thank you so much

[mh2587 · Accepted solution]

Ongoing Registrations = 
VAR SelectedMonth = SELECTEDVALUE('Calendar'[Month])
RETURN
CALCULATE(
    COUNTROWS('FactTable'),
    FILTER(
        'FactTable',
        RELATED('Calendar'[Date]) >= 'FactTable'[Start Date] &&
        (ISBLANK('FactTable'[End Date]) || RELATED('Calendar'[Date]) <= 'FactTable'[End Date])
    ),
    'Calendar'[Month] = SelectedMonth
)
//Try this one

[johnbasha33]

@zohlander 
you may want to use this
RegistrationCheck =
VAR SelectedMonth = SELECTEDVALUE('CalendarTable'[Month])
RETURN
CALCULATE(
COUNTROWS('FactTable'),
FILTER(
'FactTable',
RELATED('CalendarTable'[Date]) >= 'FactTable'[Start Date] &&
(ISBLANK('FactTable'[End Date]) || RELATED('CalendarTable'[Date]) <= 'FactTable'[End Date])
),
'CalendarTable'[Month] = SelectedMonth
)

[Anonymous · Accepted solution]

Hi @zohlander ,

@mh2587 Thanks for your concern about this case!
I build a sample data myself:

Please note that delete the relationships between the two tables:

I use this DAX to create a measure:

Measure = 
var _add=ADDCOLUMNS('fact table',
"counts",
var _date=CALENDAR([start date],[end date])
var _calendardate=VALUES('Calendar table'[Date])
RETURN COUNTROWS(INTERSECT(_date,_calendardate)))
RETURN COUNTROWS(FILTER(_add,[counts]>0))

Then the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mh2587 · Accepted solution]

Ongoing Registrations = 
VAR SelectedMonth = SELECTEDVALUE('Calendar'[Month])
RETURN
CALCULATE(
    COUNTROWS('FactTable'),
    FILTER(
        'FactTable',
        RELATED('Calendar'[Date]) >= 'FactTable'[Start Date] &&
        (ISBLANK('FactTable'[End Date]) || RELATED('Calendar'[Date]) <= 'FactTable'[End Date])
    ),
    'Calendar'[Month] = SelectedMonth
)
//Try this one