Forum Discussion

Ruksuro's avatar
Ruksuro
Helper III
9 years ago
Solved

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.

  • v-chuncz-msft's avatar
    v-chuncz-msft
    9 years ago

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

8 Replies

    • v-chuncz-msft's avatar
      v-chuncz-msft
      Community Support
      9 years ago

      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] )
                )
        )
    )
)
      • Ruksuro's avatar
        Ruksuro
        Helper III
        9 years ago

        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!