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?

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

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