Previous Year Measure for Line Chart

[etane]

Hello.

I am trying to create a YOY cumulative active customer by month line chart.  The line should show the number of customers who made at least one purchase.  One for Selected Year and another for Selected Year - 1.  And, the total is rolling, so the line should be going up over time.  

Because there is a year filter in play, I am having difficulties writing a measure that provides a PY Active Customer line for the line chart.  If I have 2026 selected, previous years' active customers is reduced.  So, I have to removefilter the Calendar.  However, this causes the PY active customer line ignore the month on the X axis resulting in a flat total line.

Please help me write a measure that accurately calculates PY active customers and still works in a line chart.   Should look something like this:

Sample file here.

Thanks.

[mizan2390 · Accepted solution]

hi @etane,

 The reason you are encountering a flat line when trying to compute the previous year is due to how ALLSELECTED and REMOVEFILTERS interact with your filter context, combined with the hardcoded year filter in your base measure.
Here is exactly what is happening:
The flat line issue: When you use REMOVEFILTERS ( 'Z - Calendar' ), DAX removes all filters on the calendar table, including the Month filter coming from your line chart's X-axis
. Without the Month filter, the measure evaluates the grand total for every point on the axis, resulting in a flat line.
The year-shift issue: In your Active Acustomrs CY (Cumulative) measure, you used ALLSELECTED('Z - Calendar'). ALLSELECTED restores the original filter context from the visual
. If you have a slicer set to 2026, ALLSELECTED will aggressively restore the 2026 filter, overriding any attempt to shift the date back to 2025. Additionally, your Active Customer CY Count measure explicitly forces 'Z - Calendar'[Year#] = CurrentYear, which locks the calculation into the currently visible year and breaks standard time-intelligence shifts.
To easily calculate a cumulative distinct count for both the current year and the previous year, the best practice is to remove the manual year overrides and use standard DAX time intelligence functions like DATESYTD and SAMEPERIODLASTYEAR.

Active Customer Base Count = 
CALCULATE(
    DISTINCTCOUNT(Order_Table[Customer_Code]),
    FILTER(
        VALUES(Order_Table[Customer_Code]),
        [A Type Order Count] > 0
    )
)

Create the CY Cumulative Measure

Instead of using ALLSELECTED, use the DATESYTD function. DATESYTD automatically modifies the filter context, gathering all dates from January 1st up to the last visible date on your chart's axis.
Because DISTINCTCOUNT is evaluated over this expanding set of dates, a customer buying in both January and February will correctly only be counted once.

Active Customers CY (Cumulative) = 
VAR TodayDate = TODAY()
VAR MaxDate = MAX('Z - Calendar'[Date])
VAR MonthStart = DATE(YEAR(MaxDate), MONTH(MaxDate), 1)

RETURN
IF(
    MonthStart > TodayDate,
    BLANK(),   -- hides months that are entirely in the future
    CALCULATE(
        [Active Customer Base Count],
        DATESYTD('Z - Calendar'[Date])
    )
)

To get the exact same cumulative calculation for the previous year, you can nest time intelligence functions. By wrapping DATESYTD inside SAMEPERIODLASTYEAR, DAX will calculate the year-to-date distinct count but safely shift the date context backward by exactly one year.

Active Customers PY (Cumulative) = 
VAR TodayDate = TODAY()
VAR MaxDate = MAX('Z - Calendar'[Date])
VAR MonthStart = DATE(YEAR(MaxDate), MONTH(MaxDate), 1)

RETURN
IF(
    MonthStart > TodayDate,
    BLANK(),
    CALCULATE(
        [Active Customer Base Count],
        SAMEPERIODLASTYEAR( DATESYTD('Z - Calendar'[Date]) )
    )
)

• By relying on DATESYTD, you no longer wipe out the month filter on the X-axis, meaning the line will correctly slope upwards rather than remaining flat.
• SAMEPERIODLASTYEAR seamlessly shifts the DATESYTD period back 12 months without requiring you to manually strip filters with REMOVEFILTERS.
• Your line chart will now correctly plot 2026 vs. 2025 cumulative active customers.

I have attached the link for the file: Customer Count Test.pbix

Please mark this if this solved your problem. 

[etane]

Thank you for your dynamic yet very easy to read dax solution.  

I only made a minor adjustment because I was expecting 357 in March PY instead of 392.  I added a YTD filter to your measure and got 357:

I am not sure why it's needed since you already have a DATESYTD function.  But, it got me the result I needed.

[mizan2390]

If your chart's axis is at "March 2026," DAX sees the entire month (March 1–31). When you use SAMEPERIODLASTYEAR( DATESYTD(...) ), DAX calculates the year-to-date dates for the entirety of March 2026, and then shifts that complete set of dates back one year. This forces DAX to evaluate the active customers all the way up to March 31, 2025, overcounting your cumulative total. 
You can also try using this DAX:

Active Customers PY (Cumulative) = 
VAR TodayDate = TODAY()
VAR MaxDate = MAX('Z - Calendar'[Date])
VAR MonthStart = DATE(YEAR(MaxDate), MONTH(MaxDate), 1)

RETURN
IF(
    MonthStart > TodayDate,
    BLANK(),
    CALCULATE(
        [Active Customer Base Count],
        CALCULATETABLE(
            SAMEPERIODLASTYEAR( DATESYTD('Z - Calendar'[Date]) ),
            KEEPFILTERS( 'Z - Calendar'[Date] <= TodayDate )
        )
    )
)

[etane]

Thank you for going above and beyond!

[Sahil_Ruchwani]

Hi @etane  you have to create a different measure for previous A type order count as well for previous year and use it. Please find below Dax:

PY A Type Order Count = --Create it first

VAR CurrentYear = MAX('Z - Calendar'[Year#]) - 1

RETURN

CALCULATE(sum(Order_Table[Case_Counter__c]),FILTER(ALL('Z - Calendar' ), 'Z - Calendar'[Year#] = CurrentYear)

, Order_Table[Product Type] in {"Alfa", "Apex"}

, Order_Table[Record_type_name__c] in {"Case","QAD Case"}

, Order_Table[Order_Status] = "Completion", Order_Table[RGB Case] = "RGB"

)
------

Active Customer PY Count = --Update this

VAR CurrentYear = MAX('Z - Calendar'[Year#]) - 1

RETURN

CALCULATE(

    DISTINCTCOUNT(Order_Table[Customer_Code]),

    FILTER(ALL('Z - Calendar'),'Z - Calendar'[Year#] = CurrentYear),

    FILTER(

        VALUES(Order_Table[Customer_Code]),

        [PY A Type Order Count]> 0 --Changed dax 

    )

)
Rest will remain same. Please find below ss:

Please mark this as solution if you find it right! Thanks

[etane]

Hi Sahil.

I created the two measures you posted in your reply.  However, the PY line didn't turn out the same as the one in your screenshot:

I didn't change anything else in the sample file.  Could I have applied something incorrectly?  The results doesn't seem to be cumulative in my file.

[v-sshirivolu]

Hi @etane ,

The issue is mainly caused by removing the date filters, which breaks the month-level context and results in a flat or incorrect previous year line. Instead of removing filters, the correct approach is to use a proper Date table and apply time intelligence functions like DATEADD, so that the month context is preserved.

I created a dedicated Date table, marked it as a date table, and established a one-to-many relationship from the Date table to the fact table. I also appended the sample data into a single table to ensure consistent data across months and years. Then, I used a base measure for active customers and built the previous year measure using DATEADD.

Since the requirement is for a rolling cumulative trend, I implemented a running total measure and applied the same logic for the previous year. Once the model, measures, and visual were aligned using Month-Year from the Date table on the axis, the chart behaved correctly with an increasing cumulative trend and a proper previous year comparison.

Any remaining gaps in the PY line are due to missing data for those months in the previous year, not an issue with the DAX logic.

Please find the attached PBIX file for reference.

Thanks.

[etane]

This doesn't seem correct.  I only provided 25-26 data but you have 23-25?

[BeaBF]

@etane Hi! I've made some fix on the pbix file.

Let me know if this supports!

BBF

💡 Did I answer your question? Mark my post as a solution!

👍 Kudos are appreciated

🔥 Proud to be a Super User!

[etane]

Thank you for your file.  However, both the CY and PY customer counts are incorrect.