Trend from History Table

[Ruksuro]

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.

[Ruksuro]

Bump, anyone got any ideas here?

Thanks.

[v-chuncz-msft · Accepted solution]

@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] )
                )
        )
    )
)

[Anonymous]

Hi @v-chuncz-msft 
I am using this DAX to be able to see how many tickets are open on a particular date.

Except in this formula it currently does not count the ticket that was opened and closed on the same date. Preferably also per department. Please see my topic here  If you could help that'd be great.

[LiamWhite]

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.

[Anonymous]

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?

[kafil10]

@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]

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

Link

I think autocalendar is doing this...

[Ruksuro]

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!