Cumulative Month-on-Month Analysis

[DataUPBI]

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

[v-yuta-msft · Accepted solution]

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]

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

[v-yuta-msft]

Hi DataUPBI,

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

Regards,

Jimmy Tao

[DataUPBI]

Total customers are calculated using

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

[v-yuta-msft]

Hi DataUPBI,

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

Regards,

Jimmy Tao