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

     

v-yuta-msft's avatar
v-yuta-msft
Community 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))

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

      Hi DataUPBI,

       

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

       

      Regards,

      Jimmy Tao

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

    Hi DataUPBI,

     

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

     

    Regards,

    Jimmy Tao