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

Bump, anyone got any ideas here?

 

Thanks.

  • 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!

       

       

       

       

    • 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...

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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?