Forum Discussion

DataUPBI's avatar
DataUPBI
Frequent Visitor
7 years ago
Solved

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

 

  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    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

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      DataUPBI
      Frequent Visitor
      7 years ago

      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's avatar
        DataUPBI
        Frequent Visitor
        7 years ago

        Total customers are calculated using

         

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