Cumulative Revenue by Region Not Calculating Correctly When Selecting a Single Customer

[GalrioSamoel]

Hi Community 

I am working on a subscription revenue report in Power BI

My data model contains:

Customers (CustomerID, Regio)

Invoices (InvoiceDate, InvoiceAmount)

Date table connected to InvoiceDate

Also I have a matrix visual:

Rows: Region → Month

Values: Monthly Revenue + Cumulative Revenue

My current cumulative measure works fine until a user selects a single customer from a slicer. Then, it only accumulates revenue for that one customer not the whole region.

This is my current measure:

Cumulative Revenue =

CALCULATE(

SUM(Invoices[InvoiceAmount]),

FILTER(

ALL(Date[Date]),

Date[Date] <= MAX(Date[Date])

)

)

I need to Keep cumulative revenue working by Region, even when a single customer is selected.

Any advice on fixing this? Thank you! 

[Kedar_Pande]

@GalrioSamoel 

Cumulative Revenue =
CALCULATE(
SUM(Invoices[InvoiceAmount]),
FILTER(
ALL(Date[Date], Customers[CustomerID]),
Date[Date] <= MAX(Date[Date])
)
)

[Ahmed-Elfeel · Accepted solution]

Hi @GalrioSamoel,

The issue happens because Customer slicer filters flow into your measure so Cumulative Revenue only calculates for the selected customer.

So you should remove the CustomerID filter while still keeping Region filter and report date filters:

Cumulative Revenue by Region =
VAR MaxVisibleDate =
    MAX ( Date[Date] )
RETURN
CALCULATE (
    SUM ( Invoices[InvoiceAmount] ),
    FILTER (
        ALLSELECTED ( Date[Date] ),
        Date[Date] <= MaxVisibleDate
    ),
    REMOVEFILTERS ( Customers[CustomerID] )  -- Important
)

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.

[GalrioSamoel]

Thanks Ahmed it worked perfectly