Forum Discussion

MarcS's avatar
MarcS
Icon for Helper I rankHelper I
5 years ago
Solved

Comulative values with categorization from other tables and timestamp manipulation

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_timecalc_subscribercalc_unsubscriber
2021-07-06 12:00125
2021-07-06 12:15148
2021-07-06 12:3009
2021-07-06 12:45243
2021-07-06 13:005224
2021-07-07 12:151201
2021-07-07 12:3023810
2021-07-08 12:3030
2021-07-08 12:3040

 

Table catagory:

created_atcat_name
2021-07-06 12:30a
2021-07-07 08:00b

 

Result:

cat_namesubscriber on createdsubscriber on created + 1 day
a13400
b53407

 

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

Need Help
  • v-jingzhang's avatar
    v-jingzhang
    5 years ago

    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.

2 Replies

  • MarcS's avatar
    MarcS
    Icon for Helper I rankHelper I
    5 years ago

    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's avatar
      v-jingzhang
      Icon for Community Support rankCommunity Support
      5 years ago

      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.