cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarcS
Helper I
Helper I

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

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

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.

View solution in original post

2 REPLIES 2
MarcS
Helper I
Helper I

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
Community Support
Community Support

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.

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Get Ready for Power BI Dev Camp

Power BI Dev Camp - September 30th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Top Solution Authors