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] )
                )
        )
    )
)
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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.