Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Solved! Go to Solution.
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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!
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
Hi @Anonymous,
You must create a measure in one of the tables with the code I provided.
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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?
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:
Any idea?
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:
Any idea?
Hi @Anonymous,
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
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]))
)
Hi @Anonymous,
I believe is that what is happening is that you are getting the total values of the Closed cases, but not sure can you possible share some sample data?
Regards,
Mfelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix,
What if instead of this calculation, I will pull into the date table, another column with the number of cases opened on each date. It would serve the same purpose. Just that not sure this is logically possible. The Open date and the close date are on the same table.
What would be the best way to privately share with you a sample data?
Thanks for your persistence (-:
Asaf
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |