cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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)

Shared Calendar nd Calc.png

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: Combining Data from multiple tables on a shared timeline

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]))
)

Backlog.png

 

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

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





View solution in original post

13 REPLIES 13
Highlighted
Super User III
Super User III

Re: Combining Data from multiple tables on a shared timeline

Hi @asaft,

 

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] ) )
        )

Preivous_Period.png

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

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Helper III
Helper III

Re: Combining Data from multiple tables on a shared timeline

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! 

Highlighted
Helper III
Helper III

Re: Combining Data from multiple tables on a shared timeline

Hi again @MFelix

 

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

 

New table.png

 

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

 

Highlighted
Super User III
Super User III

Re: Combining Data from multiple tables on a shared timeline

Hi @asaft,

 

You must create a measure in one of the tables with the code I provided.

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Helper III
Helper III

Re: Combining Data from multiple tables on a shared timeline

Hi @MFelix. I created a measure under one of the tables as follows: 

 

Count accumulate = VAR previous = MIN('Date Table'[Date])-1 VAR Ranking = RANKX (ALLSELECTED ('Date Table'[Date]), CALCULATE (SUM ('Open Cases Snapshot'[CaseNumber__c]) ),,ASC)

 

//'Date Table' is my calendar table and 'Open Cases Snapshot' is where the first column resides

 

When applying the formula I get this error 

 

The syntax for ')' is incorrect. (DAX((VAR previous = MIN('Date Table'[Date])-1 VAR Ranking = RANKX (ALLSELECTED ('Date Table'[Date]), CALCULATE (SUM ('Open Cases Snapshot'[CaseNumber__c]) ),,ASC))).

 

What am I doing wrong? 

 

Highlighted
Helper III
Helper III

Re: Combining Data from multiple tables on a shared timeline

Hi @MFelix

 

Sorry, the syntax was cut off. 

 

I modified your measure as follows: 

 

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

 

But the results I get look like that: 

 

Calc.png

 

Any idea?

Highlighted
Helper III
Helper III

Re: Combining Data from multiple tables on a shared timeline

Hi @MFelix

 

Sorry, the syntax was cut off. 

 

I modified your measure as follows: 

 

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

 

But the results I get look like that: 

 

Calc.png

 

Any idea?

Highlighted
Super User III
Super User III

Re: Combining Data from multiple tables on a shared timeline

Hi @asaft,

 

Looking at your calculation I believe that the problems is that you are making the SUM('Case'[CaseNumber]) this will give you very big value if the case number as more than 5 number. You need to change this to a count or distinct count

 

I used the sum in my case because I didn't know how you were making the count if it was a column with that value or a measure.

 

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog:

Power BI em Português





Highlighted
Helper III
Helper III

Re: Combining Data from multiple tables on a shared timeline

Hi @MFelix,

 

My columns are bases on Columns, not on measures. I just replaced all "Sum"s with "distinctcount", and I am closer but not correct yet.

 

This is what I get now

 

calc1.png

 

This is the syntax: 

 

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

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors