Calculating the cumulative values in Power BI

Question

I have two tables:

• user table (contains: user registration data. columns: user_id, create_date)
• customer order table (contains: history of orders. columns: user_id, order_date, order_id)

*user and customer aren't the same. when a user registers his first order, he becomes a customer.

For each month of each year, I want the accumulative count of distinct users and the accumulative count of the distinct customers because at last, I want to calculate the ratio of the accumulative count of the distinct customers to the accumulative count of the distinct users for each month.

I don't know how can I calculate the accumulative values and the Ratio that I said, using DAX.

Note that if a customer registers more than one order in a month, I want to count him just once for that month and if he registers a new order in the next months, also I count him in each new month.

Maybe these pictures help you to understand my question better.

-I don't count_of_users and count_of_customers columns in my tables. I should calculate them.

the user table:

user_id	create_date
1	2017-12-03
2	2018-01-01
3	2018-01-01
4	2018-02-04
5	2018-03-10
6	2018-04-07
7	2018-04-08
8	2018-09-12
9	2018-10-02
10	2018-10-02
11	2018-10-09
12	2018-10-11
13	2018-10-12
14	2018-10-12
15	2018-10-20

the customer order table:

user_id	order_date	order_id
1	2018-03-28	120
1	2018-03-28	514
1	2018-03-30	426
2	2018-02-11	125
2	2018-03-01	547
3	2018-02-10	588
3	2018-04-03	111
4	2018-02-10	697
5	2018-04-02	403
5	2018-04-05	321
6	2018-04-09	909
11	2018-10-25	8401

Answer 1

You need a few building blocks for this. Here is the data model I used:

<edit> I see user_id in the different tables are not the same, in that case you can omit the relationship between the tables and the two relationships from the Calendar table will both be active - with no need to change the relationship semantics in the count_of_customer measure. </edit>

The calendar table is important because we can't rely on one single date column to aggregate data from different tables, so we create a common calendar table with this sample DAX code:

Calendar = 
ADDCOLUMNS (
    CALENDARAUTO () ,
    "Year" , YEAR ( [Date] ) ,
    "Month" , FORMAT ( [Date] , "MMM" ) ,
    "Month-Year" , FORMAT ( [Date] , "MMM")&"-"&YEAR ( [Date] ) ,
    "YearMonthNo" , YEAR ( [Date] ) * 12 + MONTH ( [Date] ) - 1
)

Make sure to sort the Month-Year column by the YearMonthNo column so your tables look nice:

Set your relationships as shown with the active relationship from Calendar to user - if not the measures will not work unless you alter the relationships accordingly in the code! In my data model the inactive relationship is between Calendar and customer order.

Next up are the measures we will use for this. First off we count the users, a simple row count:

count_of_users = COUNTROWS ( user )

Then we count distinct user ids in the order table to count customers, here we need to use the inactive relationship between Calendar and customer order and to do this we have to invoke CALCULATE:

count_of_customers = 
CALCULATE (
    DISTINCTCOUNT ( 'customer order'[user_id] ) ,
    USERELATIONSHIP (
        'Calendar'[Date] ,
        'customer order'[order_date]
    )
)

We can use this measure to count users cumulatively:

cumulative_users = 
VAR _maxVisibleDate = MAX ( 'Calendar'[Date] ) 
RETURN
CALCULATE ( 
    [count_of_users] , 
    ALL ( 'Calendar' ) , 
    'Calendar'[Date] <= _maxVisibleDate
)

And this measure to count cumulative customers per month:

cumulative_customers = 
VAR _maxVisibleDate = MAX ( 'Calendar'[Date] ) 
RETURN
CALCULATE ( 
    SUMX ( 
        VALUES ( 'Calendar'[YearMonthNo] ) ,
        [count_of_customers] 
    ),
    ALL ( 'Calendar' ) , 
    'Calendar'[Date] <= _maxVisibleDate
)

Lastly we want the ratio of these last cumulative measures:

cumulative_customers/users = 
DIVIDE (  
    [cumulative_customers] , 
    [cumulative_users] 
)

And here is your result: