cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Count of Discrepancy Based on Date

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. Excel Marty.PNGPowerBI Marty.PNG

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!

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

 



I hope this helps,
Richard

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

Proud to be a Super User!


View solution in original post

13 REPLIES 13
Anonymous
Not applicable

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

richbenmintz
Super User
Super User

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,



I hope this helps,
Richard

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

Proud to be a Super User!


Anonymous
Not applicable

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.

 

  • However, if I had to simple calculate the open discreapancies per month based on the Discreapancy.OpenDateTime (Basically, one entry would mean 1 count for that month), how would I do that? 
  • Similarly, how would I get a count of closed discrepancies per month based on the Discrepancy.CloseDateTime?

If I can get these two values, I can simply divide them to get a ratio, correct?

 

Thanks

aj1973
Community Champion
Community Champion

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

Anonymous
Not applicable

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. fractino.PNG

Anonymous
Not applicable

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

aj1973
Community Champion
Community Champion

@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

Anonymous
Not applicable

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.

p2.PNG

 

But if I want to see data for Jan 2019, the count is messed up coz we have closed all open discrepancies. 

p1.PNG

 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. 

 

aj1973
Community Champion
Community Champion

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

 



I hope this helps,
Richard

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

Proud to be a Super User!


Anonymous
Not applicable

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?



I hope this helps,
Richard

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

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,



I hope this helps,
Richard

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

Proud to be a Super User!


Helpful resources

Announcements
May 23 2022 epsiode 5 without aka link.jpg

The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through June10th!

Power BI Dev Camp Session 22 with aka link 768x460.jpg

Check it Out!

Join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!

Top Kudoed Authors