cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DataUPBI Frequent Visitor
Frequent Visitor

Cumulative Month-on-Month Analysis

Hi, I am using just one table which has month(01/01/2018 - format), day, Number of Customers fields. I have created Cumulative Last month & Cumulative 2 month Ago measures using the below formulas.  I have month field (Jan, Feb, Mar, etc ) as slicer & "Date", "Cumulative Customers"," Cumulative Customers Last Month", "Cumulative Customers 2M Ago" as Area Chart. When I select all months in slicer then values for "Cumulative Customers"," Cumulative Customers Last Month", "Cumulative Customers 2M Ago" are displayed but when I select just one month in slicer values for only "Cumulative Customers" are displayed, but not for " Cumulative Customers Last Month", "Cumulative Customers 2M Ago". My objective is to get results for " Cumulative Customers Last Month", "Cumulative Customers 2M Ago" as well when I select any month in slicer. Not sure what is missing. Any help on this is appreciated. Thanks

 

Cumulative Customers=
CALCULATE([Total Customers],
    FILTER(ALLSELECTED(Table1 ),
        Table1[CalendarDate] <= MAX(Table1[CalendarDate])))

 

Cumulative Customers Last Month=
CALCULATE([Total Customers 1M],
    FILTER(ALLSELECTED(Table1 ),
        Table1[CalendarDate] <= MAX(Table1[CalendarDate])))

 

Cumulative Customers 2M Ago=
CALCULATE([Total Customers 2M Ago],
    FILTER(ALLSELECTED(Table1 ),
        Table1[CalendarDate] <= MAX(Table1[CalendarDate])))

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Cumulative Month-on-Month Analysis

Hi DataUPBI,

 

As a general solution , modify your measure like pattern below and check if it can meet your requirement:

Cumulative Customers =
SWITCH (
    CALCULATE (
        COUNT ( Table1[CalendarDate] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[CalendarDate] <= MAX ( Table1[CalendarDate] )
        )
    ),
    1, [Total Customers],
    2, [Total Customers 1M],
    3, [Total Customers 2M Ago]
)

Regards,

Jimmy Tao

 

View solution in original post

5 REPLIES 5
Community Support Team
Community Support Team

Re: Cumulative Month-on-Month Analysis

Hi DataUPBI,

 

As a general solution , modify your measure like pattern below and check if it can meet your requirement:

Cumulative Customers =
SWITCH (
    CALCULATE (
        COUNT ( Table1[CalendarDate] ),
        FILTER (
            ALLSELECTED ( Table1 ),
            Table1[CalendarDate] <= MAX ( Table1[CalendarDate] )
        )
    ),
    1, [Total Customers],
    2, [Total Customers 1M],
    3, [Total Customers 2M Ago]
)

Regards,

Jimmy Tao

 

View solution in original post

DataUPBI Frequent Visitor
Frequent Visitor

Re: Cumulative Month-on-Month Analysis

Hi Jimmy,

 

Thanks for looking into this. I need Cumulative numbers in 3 different fields as Cumulative Customers, Cumulative Customers 1M agao, Cumulative Customers 2M ago.

 

Regards

 

Highlighted
DataUPBI Frequent Visitor
Frequent Visitor

Re: Cumulative Month-on-Month Analysis

Total customers are calculated using

 

Total Customers 1M Ago = CALCULATE([Customers], DATEADD(Table1[CalendarDay], -1, MONTH))

Community Support Team
Community Support Team

Re: Cumulative Month-on-Month Analysis

Hi DataUPBI,

 

Could you share some sample data and clarify more details about your requirement?

 

Regards,

Jimmy Tao

Community Support Team
Community Support Team

Re: Cumulative Month-on-Month Analysis

Hi DataUPBI,

 

Have you solved your problem? Could you kindly mark my answer?

 

Regards,

Jimmy Tao

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 285 members 2,607 guests
Please welcome our newest community members: