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] )
                )
        )
    )
)
kafil10's avatar
kafil10
Frequent Visitor
9 years ago

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

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

     

    Link

     

    I think autocalendar is doing this...