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...
  • 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] )
                )
        )
    )
)
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] )
                )
        )
    )
)
LiamWhite's avatar
LiamWhite
Advocate I
7 years ago

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.