Forum Discussion

SammyNed's avatar
SammyNed
Helper I
5 years ago
Solved

Cumulative Sum

Hi team,   I could really use some help. i have a data table similar to that below basically showing Customer ID's and Dates that they placed an Order. I want to create a graph as below that basica...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    5 years ago

     

    Loyal Customers count =
    VAR _currentmonthyear =
    MAX ( Dates[End of Month] )
    VAR _untilcurrentmonthyeartable =
    FILTER ( ALL ( Sales ), Sales[Order Date] <= _currentmonthyear )
    VAR _customerscounttable =
    GROUPBY (
    _untilcurrentmonthyeartable,
    Sales[Customer ID],
    "@customerscount", SUMX ( CURRENTGROUP (), 1 )
    )
    VAR _loyalcustomerstable =
    FILTER ( _customerscounttable, [@customerscount] > 2 )
    RETURN
    COUNTROWS ( _loyalcustomerstable )

     

     

    Link to the PBIX file 

     

     

Jihwan_Kim's avatar
Jihwan_Kim
Super User
5 years ago

 

Loyal Customers count =
VAR _currentmonthyear =
MAX ( Dates[End of Month] )
VAR _untilcurrentmonthyeartable =
FILTER ( ALL ( Sales ), Sales[Order Date] <= _currentmonthyear )
VAR _customerscounttable =
GROUPBY (
_untilcurrentmonthyeartable,
Sales[Customer ID],
"@customerscount", SUMX ( CURRENTGROUP (), 1 )
)
VAR _loyalcustomerstable =
FILTER ( _customerscounttable, [@customerscount] > 2 )
RETURN
COUNTROWS ( _loyalcustomerstable )

 

 

Link to the PBIX file 

 

 

SammyNed's avatar
SammyNed
Helper I
5 years ago

Works perfectly.

Thanks Jihwan_Kim