Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Combining Data from multiple tables on a shared timeline

Hi, 

 

Struggling with the following task. I have two tables (see image), both with timelines, and count of items per date. What I am trying to achieve is: 

 

1. Show data from both count columns on one shared timeline

2. Calculate data between two columns as follows: (#Items in Table1, in a given date)-(#Items in Table1, in a previous date)+(#Items in Table2, in a given date) 

3. Name the new calculated column

4. Display all 3 columns on a column+Line graph,

 

Thanks! 

 

(Example in the attached image)

 

 

 

 

Need Help
  • MFelix's avatar
    MFelix
    9 years ago

    Hi @asaft,

     

    Just created a model with two table with your data Backlog and Closed cases mades changes to distinct count on your measure and got the result below.

     

    Count accumulate = 
VAR previous =
MIN ('Date Table'[Date]) - 1
VAR Ranking =
RANKX (
ALLSELECTED ('Date Table'[Date]);
CALCULATE (DISTINCTCOUNT(Backlog[Case Number]));
;
ASC
) //Calculates the ranking based on the period number in order to work with different period slicers
RETURN
DISTINCTCOUNT(Backlog[Case Number])
- CALCULATE (
DISTINCTCOUNT(Backlog[Case Number]);
FILTER (ALL(Backlog);Backlog[Snapshot Created Date] = Previous )
) //Calculation of variation to previous period
+ CALCULATE (
DISTINCTCOUNT('Closed Cases'[Case Number]);
FILTER(ALL('Closed Cases'); 'Closed Cases'[Date/Time Closed] = MIN('Date Table'[Date]))
)

     

    Believe everything is as needed. Please check it and tell me.

     

    Regards,

    MFelix

13 Replies

  • MFelix's avatar
    MFelix
    Super User
    9 years ago

    Hi Anonymous,

     

    Assuming you are using a calendar table and have that relates to your other two tables use this formula:

     

    Count accumulate =
VAR previous =
    MIN ( 'Calendar'[Date] ) - 1
VAR Ranking =
    RANKX (
        ALLSELECTED ( 'Calendar'[Date] ),
        CALCULATE ( SUM ( Table1[Count Table 1] ) ),
        ,
        ASC
    ) //Calculates the ranking based on the period number in order to work with different period slicers
RETURN
    SUM ( Table1[Count Table 1] )
        - CALCULATE (
            SUM ( Table1[Count Table 1] ),
            FILTER ( ALL ( Table1 ), Table1[Snapshot Date] = Previous )
        ) //Calculation of variation to previous period
        + CALCULATE (
            SUM ( Table2[Count Table 2] ),
            FILTER ( ALL ( Table2 ), Table2[Closed Date] = MIN ( 'Calendar'[Date] ) )
        )

    First Value is 53 since my data only starts at 03/06/2017

     

    Regards,

    MFelix

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi MFelix,

       

      Thanks so much for your prompt reply. I have to admit that this is an advanced solution for me, so I would need also to understand the preliminary step of creating a calendar table and relating these two columns for it. Also, after having the calendar table, where and how do I use the query that you have created?

       

      Thanks again! 

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi again MFelix

         

        Using "Manage Relationship" I could relate the two calculated columns to a Date table as you can see below: 

         

         

        Now, when I look at the query editor I cannot find the new table that I have created.... How do I use the formula that you have provided then? 

         

        Thanks