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

 

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

 

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

 

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

Highlighted
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
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 285 members 3,061 guests
Please welcome our newest community members: