calculate cumulative sales for active products

[Mario_Sam]

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!

[Ahmed-Elfeel · Accepted solution]

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.

[Kedar_Pande]

@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
)
)

[wardy912 · Accepted solution]

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!

[Ahmed-Elfeel · Accepted solution]

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.

[Mario_Sam]

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?

[Mauro89]

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

[Mario_Sam]

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