Comulative values with categorization from other tables and timestamp manipulation

[MarcS]

Hi,

I have two tables. One "subscriber" stores subscriber and unsubscriber values for a timestamp.
The other table has only one product category with a creation date:

Now I want to show visually in a bar chart, how many subscribers were present when the category was created and how many after one day:

Table subscriber:

date_time	calc_subscriber	calc_unsubscriber
2021-07-06 12:00	12	5
2021-07-06 12:15	14	8
2021-07-06 12:30	0	9
2021-07-06 12:45	24	3
2021-07-06 13:00	52	24
2021-07-07 12:15	120	1
2021-07-07 12:30	238	10
2021-07-08 12:30	3	0
2021-07-08 12:30	4	0

Table catagory:

created_at	cat_name
2021-07-06 12:30	a
2021-07-07 08:00	b

Result:

cat_name	subscriber on created	subscriber on created + 1 day
a	13	400
b	53	407

For this purpose, I have calculated a comulative subscriber value via a calculate coulmn:

comulative subscriber_on_created = 

CALCULATE(
    SUM(subscribers[calc_subscribers]),FILTER(ALL(subscribers),subscribers[date_time] <= category[created_at])))-
CALCULATE(
  SUM(subscribers[calc_unsubscribers]),FILTER(ALL(subscribers),subscribers[date_time] <= category[created_at])))

Now I am still looking for a way for the second calculated column. I tried for category [created_at] with Dateadd(date,1,day), Date (year, month, day + 1), date.dateAdd.

And second question. Is this the most performant way with one million entries in subscriber?

Best regards

[MarcS]

After a little more research i found a trivial solution: 

comulative subscriber_on_created = 

CALCULATE(
    SUM(subscribers[calc_subscribers]),FILTER(ALL(subscribers),subscribers[date_time] <= (category[created_at]+1)))-
CALCULATE(
  SUM(subscribers[calc_unsubscribers]),FILTER(ALL(subscribers),subscribers[date_time] <= (category[created_at]+1)))

 +1 adds directly 1 day to the timestamp. Little bit confusing but it works.

Open is the question about the performance for more than one calculated colum. If i have 20 of these calculate columns for +1 day +2 days +5 days .... with a subscriber table with over 1 mio entries. Is there a better solution here.

[v-jingzhang · Accepted solution]

Hi @MarcS 

You can try modifying the column code to below one.

comulative subscriber_on_created =
CALCULATE (
    SUM ( subscribers[calc_subscribers] ) - SUM ( subscribers[calc_unsubscribers] ),
    FILTER (
        ALL ( subscribers ),
        subscribers[date_time] < ( category[created_at] + 1 )
    )
)

It seems you are adding calculated columns in Table category. If so, it will enlarge the model size a little bit. This is ok. If you add 20 of these columns to Table subscriber with over 1 mio entries, the model size will be very large. You could use measures rather than calculated columns to avoid enlarging model size.

Create a base measure

comulative subscriber = SUM ( subscribers[calc_subscribers] ) - SUM ( subscribers[calc_unsubscribers] )

Then create measures for different days. You can modify the days variable to create 20 measures. 

comulative subscriber + 1 day =
VAR days = 1 // change days value from 0 to 20
RETURN
    CALCULATE (
        [comulative subscriber],
        FILTER (
            ALL ( subscribers ),
            subscribers[date_time] < MAX ( category[created_at] ) + 1 + days
        )
    )

You can also pass a what-if parameter or axis values to the days variable in this measure when you want it to change dynamically according to context in visuals. 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.