Measure that uses quarter filter to compare year

[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	CreatedDate	Status
2024 Q1	01/02/2023	Completed
2024 Q2	19/04/2024	Completed
2024 Q3	13/07/2024	In progress
2024 Q4	22/12/2025	Completed
2025 Q1	12/12/2024	In progress
2025 Q2	30/11/2025	Completed
2025 Q3	27/09/2025	In progress
2025 Q4	23/04/2025	Completed

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 the Status is "Completed" and the CreatedDate has the same year as the user filter in the Year-Quarter filter.

Example 1: If the user filters 2025 Q-3, the measure should show the value 2 because of all the rows where the CreatedDate was in 2025 and the Year-Quarter is 2025 as well, there are two rows with "Completed" status.

Example 2: If the user filters 2024 Q-1, the measure should show the value 1 because of all the rows where the CreatedDate was in 2024 and the Year-Quarter is 2024 as well, there is only one rowswith "Completed" status.

How can I do that measure?

[DataNinja777]

Hi @nok ,

You can achieve this with a DAX measure that uses a combination of CALCULATE, FILTER, ALL, and SELECTEDVALUE. This approach allows the measure to correctly identify the year from the slicer and apply it as a filter condition across the entire Sales table, regardless of the specific quarter selected.

Completed Sales by Selected Year =
VAR SelectedYear =
    SELECTEDVALUE ( 'Calendar'[Year] )
RETURN
    CALCULATE (
        COUNTROWS ( 'Sales' ),
        FILTER (
            ALL ( 'Sales' ),
            'Sales'[Status] = "Completed"
                && YEAR ( 'Sales'[CreatedDate] ) = SelectedYear
        )
    )

This measure first declares a variable, SelectedYear, which captures the single year value from your slicer's context using SELECTEDVALUE. For instance, if '2025 Q3' is selected, this variable holds the value 2025. The CALCULATE function then modifies the context for the COUNTROWS calculation. The key is the FILTER logic. By using ALL('Sales'), we tell the formula to remove the initial filter from the slicer (the specific quarter) and look at the entire Sales table. It then iterates through every row of this unfiltered table and applies new criteria: it keeps only the rows where the Status is "Completed" and where the year of the CreatedDate matches the SelectedYear variable. The final result is a count of all rows that meet these two conditions for the selected year.

Best regards,

[KarinSzilagyi]

Hi @DataNinja777, I like your solution, but with your Measure I get 3 completed if I select 2025 Q3 rather than 2 as in OPs example:

[KarinSzilagyi · Accepted solution]

Hi @nok, did you mean a measure like this?

Completed same year as selection = 
VAR SelYear = SELECTEDVALUE ( DimDate[Year] )
RETURN
IF (
    ISBLANK ( SelYear ),
    BLANK (),
    CALCULATE (
        COUNTROWS ( FactSales ),
        FactSales[Status] = "Completed",
        FILTER (
            ALL ( FactSales[Year-Quarter] ),
            VALUE ( LEFT ( FactSales[Year-Quarter], 4 ) ) = SelYear
        ),
        FILTER (
            ALL ( FactSales[CreatedDate] ),
            YEAR ( FactSales[CreatedDate] ) = SelYear
        )
    )
)

a