DAX Measurement Issue: Counting SKUs Since September Last Year Without Relying on the Month Filter

[Syndicate_Admin]

Hello

I'm creating a measure in Power BI to count the average SKUs a company has from September of last year to the month selected in the data slicer. However, I need you to:

1. If I select January of the current year, the measure already includes SKUs sold from September to December of last year. That is, skus from September to December must be included in January of the current year.
2. If I select any other month in cumulative, I need it to include SKUs sold in September-December of last year plus those in January up to the selected month of the current year.
3. The month filter should affect relatively, for example, if I select the month of September I only want to see the skus for September 2025. I don't want it to add those of 2024 since these, along with those of November and December 2024, should be considered only in January 2025.
4. Other filters (example: Customer, Category, Range) must continue to work.

Available data:

• Combine CY (Current Year SKUs) → Key spgr
• Combine PY (last year's SKUs) → Key spgr
• I don't have a "Year" column, just the Calendar Month column (with values from 1 to 12).

Current measures:

• To count SKUs in the current year:

• To calculate the average SKUs:

Avg. Sku CY (v2) = Medidas[Skus CY (v2)] / [Distribución TOTAL CY]

I've tried several solutions with REMOVEFILTERS(), ALL(), and ALLSELECTED(), but when I change the "Month" filter in Power BI, the measure still gets affected.

How do I write a measure in DAX that counts SKUs from September last year to the selected month, ensuring that the month filter only affects relatively and without losing the other filters?

[DataNinja777 · Accepted solution]

Hi @Syndicate_Admin ,

To count SKUs from September of the previous year to the selected month of the current year while ensuring that the month filter affects relatively, you can use CALCULATE, FILTER, and DATESBETWEEN in DAX. Since your dataset does not contain a "Year" column and only has a "Calendar Month" column (values 1 to 12), the formula infers the relative year dynamically based on the selected month.

The following measure calculates the distinct count of SKUs while ensuring that SKUs from September to December of the previous year are included when selecting months in the current year. If January is selected, it includes SKUs from September to December of the prior year as well as those in January. For any other month, it accumulates the SKUs from September to the selected month.

Skus CY Adjusted = 
VAR SelectedMonth = MAX('Calendar'[Calendar Month])
VAR CurrentYear = YEAR(TODAY()) 
VAR StartDate = DATE(CurrentYear - 1, 9, 1) 
VAR EndDate = DATE(CurrentYear, SelectedMonth, 1) 

RETURN
CALCULATE(
    DISTINCTCOUNT('Combine CY'[Key spgr]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Date] >= StartDate &&
        'Calendar'[Date] <= EndDate
    )
)

This measure works by first determining the selected month from the slicer, retrieving the current year, and calculating the start date as September 1st of the previous year while dynamically setting the end date as the selected month of the current year. The CALCULATE function then filters the dataset to include all SKUs within this range, ensuring that the measure reflects the required logic without being completely overridden by the slicer.

If your data model only contains a Calendar Month column (1-12) without a full date field, you may need to adjust this formula by creating a derived year or using a Year-Month column instead. Let me know if adjustments are needed based on your specific table structure.

Best regards,