topic Re: calculate cumulative sales for active products in DAX Commands and Tips

calculate cumulative sales for active products

Mario_Sam — Tue, 09 Dec 2025 13:12:23 GMT

I work on a project for a retail company that sells products across multiple regions. In my Power BI model I have the following tables:

1-SalesOrderID:

-CustomerID

-ProductID

-OrderDate

-SalesAmount

2-Customer

-CustomerID

-CustomerName

-Region

-CustomerTier (Bronze, Silver, Gold)

3-Product

-ProductID

-ProductName

-Category

-ActiveFlag (1 = active, 0 = inactive)

Firstly I want to calculate cumulative sales for active products only, broken down by customer tier, but I only want to include customers who have made at least 3 purchases in the current year

Also I want the cumulative sales to reset at the start of each year

I tried the following DAX measure, but it does not give the correct result:

Cumulative Sales I Tried =

CALCULATE(

SUM(Sales[SalesAmount]),

Sales[OrderDate] <= MAX(Sales[OrderDate]),

Product[ActiveFlag] = 1,

Customer[CustomerTier] = "Gold"

)

I need help in this also I asked gpt but it is wasting my time,Thanks in advance!

Re: calculate cumulative sales for active products

Mauro89 — Tue, 09 Dec 2025 14:15:13 GMT

Hi Mario_Sam,

give this one a try:

Cumulative Sales Active Products = 
VAR CurrentYear = YEAR(MAX(Sales[OrderDate]))
VAR CurrentDate = MAX(Sales[OrderDate])
VAR QualifiedCustomers = 
    FILTER(
        VALUES(Customer[CustomerID]),
        CALCULATE(
            DISTINCTCOUNT(Sales[SalesOrderID]),
            YEAR(Sales[OrderDate]) = CurrentYear
        ) >= 3
    )
RETURN
CALCULATE(
    SUM(Sales[SalesAmount]),
    FILTER(
        ALL(Sales[OrderDate]),
        Sales[OrderDate] <= CurrentDate &&
        YEAR(Sales[OrderDate]) = CurrentYear
    ),
    Product[ActiveFlag] = 1,
    Customer[CustomerID] IN QualifiedCustomers
)

Best regards!

PS: If you find this post helpful consider leaving kudos or mark it as solution

Re: calculate cumulative sales for active products

Mario_Sam — Tue, 09 Dec 2025 15:22:31 GMT

Thanks for your replying but some customers are included even though they dont have 3 purchases in the current year also other qualified customers are being excluded randomly when I change filters...But Thanks for your reply

Re: calculate cumulative sales for active products

Ahmed-Elfeel — Tue, 09 Dec 2025 15:24:50 GMT

Hi Mario_Sam,

I hope you are doing well today ☺️❤️

So Could you try this and tell me if it works:

Cumulative Sales Active Products = VAR CurrentYear = YEAR(MAX(Sales[OrderDate])) VAR CurrentDate = MAX(Sales[OrderDate]) VAR QualifiedCustomers = FILTER( VALUES(Customer[CustomerID]), CALCULATE( DISTINCTCOUNT(Sales[SalesOrderID]), Sales[CustomerID] = Customer[CustomerID], YEAR(Sales[OrderDate]) = CurrentYear ) >= 3 ) VAR ActiveProducts = FILTER( VALUES(Product[ProductID]), Product[ActiveFlag] = 1 ) RETURN CALCULATE( SUM(Sales[SalesAmount]), FILTER( ALL(Sales[OrderDate]), Sales[OrderDate] <= CurrentDate && YEAR(Sales[OrderDate]) = CurrentYear ), Sales[ProductID] IN ActiveProducts, Customer[CustomerID] IN QualifiedCustomers )

if this post helps, then I would appreciate a thumbs up and mark it as the solution to help the other members find it more quickly.

Re: calculate cumulative sales for active products

Mario_Sam — Tue, 09 Dec 2025 15:32:37 GMT

Thanks Ahmed For the Solution, I have another question:
How can I modify this cumulative sales measure to also break it down by Customer Tier dynamically, so I can see cumulative sales per tier while still considering only active products and customers with at least 3 purchases?

Re: calculate cumulative sales for active products

wardy912 — Tue, 09 Dec 2025 15:33:57 GMT

Hi Mario_Sam

My first suggestion would be to add a date table, this is always useful and makes time intelligence reliable

Date = VAR MinDate = CALCULATE( MIN ( Sales[OrderDate] ), REMOVEFILTERS() ) VAR MaxDate = CALCULATE( MAX ( Sales[OrderDate] ), REMOVEFILTERS() ) RETURN ADDCOLUMNS ( CALENDAR ( MinDate, MaxDate ), "Year", YEAR ( [Date] ), "MonthNumber", MONTH ( [Date] ), "MonthName", FORMAT ( [Date], "mmm" ) )

Mark it as Date table (Modeling - Mark as date table, column = Date[Date]).
Relate Date[Date] to Sales[OrderDate].

Next, add a couple of base measures:

Sales Amount = SUM ( Sales[SalesAmount] )

Orders = DISTINCTCOUNT ( Sales[SalesOrderID]

Orders in Selected Year = VAR SelectedYear = MAX ( Date[Year] ) RETURN CALCULATE ( [Orders], FILTER ( ALL ( Date[Date] ), Date[Year] = SelectedYear ) )

Now you can add your cumulative measure:

Cumulative Sales (Active, Tier, Customers ≥3, Year Reset) = VAR CurrentDate = MAX ( Date[Date] ) VAR SelectedYear = MAX ( Date[Year] ) RETURN CALCULATE ( [Sales Amount], KEEPFILTERS ( Product[ActiveFlag] = 1 ), KEEPFILTERS ( FILTER ( VALUES ( Customer[CustomerID] ), [Orders in Selected Year] >= 3 ) ), FILTER ( ALL ( Date[Date] ), Date[Year] = SelectedYear && Date[Date] <= CurrentDate )

--------------------------------

I hope this helps, please give kudos and mark as solved if it does!

Connect with me on LinkedIn.

Subscribe to my YouTube channel for Fabric/Power Platform related content!

Re: calculate cumulative sales for active products

Kedar_Pande — Wed, 10 Dec 2025 06:16:02 GMT

Mario_Sam

Cumulative Sales Active =
VAR CurrentDate = MAX(SalesOrderID[OrderDate])
VAR CurrentYear = YEAR(CurrentDate)
VAR YearStart = DATE(CurrentYear, 1, 1)

VAR QualifyingCustomers =
CALCULATETABLE(
VALUES(Customer[CustomerID]),
SalesOrderID[OrderDate] >= YearStart,
FILTER(
Customer,
CALCULATE(COUNTROWS(SalesOrderID)) >= 3
)
)

RETURN
CALCULATE(
SUM(SalesOrderID[SalesAmount]),
FILTER(
ALL(SalesOrderID),
SalesOrderID[OrderDate] <= CurrentDate &&
YEAR(SalesOrderID[OrderDate]) = CurrentYear &&
SalesOrderID[CustomerID] IN QualifyingCustomers &&
RELATED(Product[ActiveFlag]) = 1
)
)