Count rows for the next six months based on the Quarter dynamically

[nok]

Hi!

I have two tables that follow this structure:
Calendar

Year-Quarter	Year
2024 Q1	2024
2024 Q2	2024
2024 Q3	2024
2024 Q4	2024
2025 Q1	2025
2025 Q2	2025
2025 Q3	2025
2025 Q4	2025

Sales

Year-Quarter	FileDate	PlannedCompletionDate	Status
2024 Q1	23/03/2024	01/02/2023	X
2024 Q2	19/06/2024	19/04/2024	ScheduledForCompletion
2024 Q3	30/09/2024	13/07/2024	X
2024 Q4	13/12/2024	22/12/2024	ScheduledForCompletion
2025 Q1	30/03/2025	04/02/2025	ScheduledForCompletion
2025 Q2	30/06/2025	30/11/2025	X
2025 Q3	30/09/2025	19/09/2025	X
2025 Q3	30/09/2025	27/09/2025	ScheduledForCompletion
2025 Q4	26/12/2025	23/11/2025	ScheduledForCompletion

In my dashboard, I have a "Year-Quarter" filter coming from the Calendar table. My two tables are related by the Year-Quarter column. I want to create a measure that counts rows in my Sales table where it checks, in the FileDate column, which date refers to the Year-Quarter filtered by the user and then counts the number of rows where the Status column = "ScheduledForCompletion" and the PlannedCompletionDate column is in the next 6 months from the specified FileDate.

Example 1: If the user filters 2025 Q2, the measure should show the value 3 because based on the FileDate for 2025 Q2 (30/06/2025), there are three rows with "ScheduledForCompletion" status in the next six months:

2025 Q3	30/09/2025	27/09/2025	ScheduledForCompletion
2025 Q4	26/12/2025	23/11/2025	ScheduledForCompletion
2024 Q4	13/12/2024	22/12/2025	ScheduledForCompletion

Example 2: If the user filters 2024 Q4, the measure should show the value 2 because based on the FileDate for 2024 Q4 (13/12/2024), there are two rows with "ScheduledForCompletion" status in the next six months:

2024 Q4	13/12/2024	22/12/2024	ScheduledForCompletion
2025 Q1	30/03/2025	04/02/2025	ScheduledForCompletion

How can I do that measure?

[KarinSzilagyi · Accepted solution]

@nok Hello again 😅

Did you mean something like this:

Count Scheduled Next 6M = 
VAR SelQ =
    SELECTEDVALUE ( 'Calender'[Year-Quarter] )
VAR RefDate =
    CALCULATE (
        MAX ( FactSales[FileDate] ),
        FILTER (
            ALL ( FactSales[Year-Quarter], FactSales[FileDate] ),
            FactSales[Year-Quarter] = SelQ
        )
    )
VAR EndDate = EDATE ( RefDate, 6 )
RETURN
IF (
    ISBLANK ( RefDate ),
    BLANK (),
    CALCULATE (
        COUNTROWS ( FactSales ),
        KEEPFILTERS ( FactSales[Status] = "ScheduledForCompletion" ),
        KEEPFILTERS ( FactSales[PlannedCompletionDate] >= RefDate ),
        KEEPFILTERS ( FactSales[PlannedCompletionDate] <= EndDate ),
        ALL ( 'Calender' ),
        ALL ( FactSales[Year-Quarter] )
    )
)

[d_m_LNK]

I would start with something like this below.  Also Maybe consider adding a date column to the calendar table and join the calendar table to the filedate column (if possible):

VAR _sixMonths = DateAdd(Sales[FileDate], 6, Month)

VAR _FileDate = SELECTEDVALUE(Sales[Filedate))

VAR Result = 

CALCULATE(COUNTROWS(Sales), Sales[ScheduledForCompletion] = "ScheduledForCompletion", DatesBetween(Sales[PlannedCompletionDate], _FileDate, _sixMonths))

RETURN Result