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

     

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