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 i...
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.

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.