cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Ruksuro Member
Member

Trend from History Table

Hi Community!

 

Apologies if this has been raised previously or is very simple but I can't seem to find a clear soltuion to the following...

 

Given a source history table (think subscriptions to a service) for can I easily show a count over time as a trend?

 

Table Structure:

Customer ID (int) | Subscribed From (date) | Subscribed Until (date)

 

I want to display a graph which shows the number of active subscriptions over time.

 

The logic (in no particular language) would read as:

 

count(distinct

    IF( [axis date] >= Subscribed From AND

          ( [axis date] < Subscribed Until OR Subscribed Until IS NULL)

    THEN

       Customer ID

    ELSE

        NULL

)      

 

This is possible in Qlikview (bit of a hack) but I'm strugging with PowerBI

 

Any help appreciated, thanks.

 

Harry.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Trend from History Table

@Ruksuro,

 

You may refer to the following DAX that creates a new table.

Table =
ADDCOLUMNS (
    CALENDARAUTO (),
    "Count", CALCULATE (
        DISTINCTCOUNT ( Table1[Customer ID] ),
        FILTER (
            Table1,
            Table1[Subscribed From] <= [Date]
                && (
                    Table1[Subscribed Until] > [Date]
                        || ISBLANK ( Table1[Subscribed Until] )
                )
        )
    )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
7 REPLIES 7
Ruksuro Member
Member

Re: Trend from History Table

Bump, anyone got any ideas here?

 

Thanks.

Community Support Team
Community Support Team

Re: Trend from History Table

@Ruksuro,

 

You may refer to the following DAX that creates a new table.

Table =
ADDCOLUMNS (
    CALENDARAUTO (),
    "Count", CALCULATE (
        DISTINCTCOUNT ( Table1[Customer ID] ),
        FILTER (
            Table1,
            Table1[Subscribed From] <= [Date]
                && (
                    Table1[Subscribed Until] > [Date]
                        || ISBLANK ( Table1[Subscribed Until] )
                )
        )
    )
)
Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Ruksuro Member
Member

Re: Trend from History Table

Thanks for this v-chuncz-msft, worked perfectly.

 

Just for everyone else's information, this solution is not fast (given it's having to count each row multiple times it's not surprising). For a large dataset you might want this info precalculated at source.

 

Very impressed with DAX so far!

 

 

 

 

kafil10 Frequent Visitor
Frequent Visitor

Re: Trend from History Table

@v-chuncz-msft

 

i tried this the figures don't much up really and the table begun from 01/01/1899 ??

 

Table =
ADDCOLUMNS (
    CALENDARAUTO();
    "Count"; CALCULATE (
        COUNT ( Merge1[CallID] );
        FILTER (
            Merge1;
            Merge1[NewColumn.DateAssign] <= [Date]
                && (
                   Merge1[NewColumn.DateResolv] > [Date]
                        || ISBLANK ( Merge1[ClosedDate] )
                )
        )
    )
)

Ruksuro Member
Member

Re: Trend from History Table

Can't say for sure but suspect this is due to your data, please see the following.

 

Link

 

I think autocalendar is doing this...

Highlighted
kristen Regular Visitor
Regular Visitor

Re: Trend from History Table

Thank you for this, @v-chuncz-msft! Worked beautifully.

 

What if I wanted to add another column that showed the number of subscribers that had been with us a year each day?

LiamWhite Frequent Visitor
Frequent Visitor

Re: Trend from History Table

Thanks @v-chuncz-msft I have used your recommended DAX, but I can't see how this will work for visualisations.

 

It seems like it just creates a table that counts each time increment for each year. How would you set up the visualisation so that you can identify open subscriptions over different time periods?

 

Cheers.