Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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.

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.