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

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
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.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 134 members 1,678 guests
Please welcome our newest community members: