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.
Hello,
We're trying to count the # of discrepancies occuring over time. The thing is, once a discrepancy has been closed, it's status appears as closed in the table (Check field Discrepancy.Status).
I want to find the number of Discrepancies opened each Month and compare it to the number of Discrepancies closed each month.
Note, Discreapancy can be closed on a different month than it was opened on.
The first image is to show what we have on excel today and how I'm trying to build it on Power BI.
The second image is a snpashot of the existing table in Power BI.
I understand this question has been askedmany times before, but it is hard to understand another user's perspective. Hence posting this again.
Thank You!
Solved! Go to Solution.
Hi @aj1973 ,
Add another relationship from your date table to your facts on the close date. you can then create a measure that counts the closed by date as
closed = calculate(countrows('table'), 'date_closed' <> blank(), userelationship('date'[date], table[date_closed]))
Proud to be a Super User!
Thanks @aj1973 and @richbenmintz . Apologies for the delayed response.
The last post mentions
closed = calculate(countrows('table'), 'date_closed' <> blank(), userelationship('date'[date], table[date_closed]))
This works great! this is exactly what I was looking for. It matches with everything I have.
One follow up question would be - how can I find the Open Discrepancy count?
Will it be Open = countrows('table') - closed
Unfortunately, my limited knowledge in Database Design restricts my understanding of the impliactions of having 2 fields linked to a date field. Hence I ask this rather obvious question
Hi @Anonymous
Assuming you have a date table the following psuedo dax formula should do the trick
Closed Discrepancies rate=
var _all_desc = CALCULATE(COUNTROWS('TABLE'),all(TABLE[STATUS]))
return
divide(CALCULATE(COUNTROWS('TABLE'), TABLE[STATUS]=OPEN), var_all_desc)
Hope this helps,
Proud to be a Super User!
Wow, thanks for the quick response @richbenmintz.
I do have a date table with info like FiscalYearOffset, CurYearOffSet, Weekday,Quarter, etc.
The formula you gave is not giving the desired result. That's probably because I don't understand VAR function so well.
If I can get these two values, I can simply divide them to get a ratio, correct?
Thanks
Hi @Anonymous
The formula provided by @richbenmintz is doing exactly what you need, It is deviding the number of Opened.Desc by the Total Number of Closed.Desc + opened.
change this
var _all_desc = CALCULATE(COUNTROWS('TABLE'),TABLE[STATUS]= Closed))
you will get what you need.
Now if you want to filter the measure by Month, Week, Year or Quarter the use a Date slicer in your page report.
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
Thanks @aj1973
The formula is correct but the problem is that the Discrepancy.Status field updates daily and will change a discrepancy that is 'Open' to 'Close' once the job is done.
let's assume that there are 10 cases open today. If they close tomorrow (1/26/2021) and if I run a ratio of Closed to Open, I will get a very low fraction.
What I want instead, is to check the date a discrepancy has been filed (which is the Discrepancy.OpenDateTime) and keep a track of the count. So, if there are 10 entries in Jan, my count of OpenDiscrepancies will be 10. And if 4 are closed (see Discrepancy.ClosedDateTime) in Jan, 3 in Feb and 3 still unclosed (the Discrepancy.ClosedDateTime field will be blank in this case) I should have a ClosedDiscrepancies count like below -
Month OpenDiscrepancyCount ClosedDiscrepancyCount
Jan 10 4
Feb 0 3
Mar 0 0
@Anonymous
What's wrong with the fraction getting low!? You update the report and the measure updates accordingly. sorry but I don't see the issue
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
I guess I'm not explaining it properly. My apologies.
We'll get correct results if we look at current month's data. See below image. We had 48 Cases opened out of 57. So the function can get a result based on the Discrepancy.Status field.
But if I want to see data for Jan 2019, the count is messed up coz we have closed all open discrepancies.
The point I am making is that my calculation of Open/Closed Discrepanices should not be based on the Discrepancy.Status field but simply on the date it was posted - which captured by the fields Discrepancy.OpenDateTime and Discrepancy.CloseDateTime.
I don't know how to make a count based on date field instances.
Basically, every row has an OpenDate and a ClosedDate. How can I create 2 measures - one for OpenDate and another for ClosedDate - that add each instance.
@Anonymous
I see, you want to see the history of that rate. Well, am almost sure that Power Bi Model doesn't have the ability to record past data. When you refresh your Model the power bi desktop shows what it exsists in the table for that instance. So if in your database of that Table the rows are overwritten, Power bi can't help it. However check if there is a History_Table in the database that keeps track of all transactions(I am sure there is one) and use it in your model.
Regards
Amine Jerbi
If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook
Hi @aj1973 ,
Add another relationship from your date table to your facts on the close date. you can then create a measure that counts the closed by date as
closed = calculate(countrows('table'), 'date_closed' <> blank(), userelationship('date'[date], table[date_closed]))
Proud to be a Super User!
Thank you so much for this! It is working as expected. I'm getting the correct results for Closed Cases. How then, can I use a similar formula to capture Open Discrepancies?
Will it be Open = Calculate(Countrows('discrepancyTable'), 'OpenDateTime' <> Blank, Userelationship('date'[date], table[OpenDateTime]))??
Hi @Anonymous ,
If the active relationship between the date table and the discrepancy table is materialized through the opendatetime column then you would only need the following
Open = Countrows('discrepancyTable')
This will count all of the rows when they where opened regardless of state.
Make Sense?
Proud to be a Super User!
Hi @Anonymous ,
Not sure why you un-accepted the solution, if possible could you accept the solution as it makes it easier for other users to find answers in the forum.
Thanks,
Proud to be a Super User!
Covering 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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |