Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreShape the future of the Fabric Community! Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions. Take survey.
I will attache a link to a sample file.
In this file I have this setup:
So an autocalendar that is linked to the table Case based on CreateDate. But there is also ClosedDate.
From the table case I want to count the caseId's. These are unique and when I count them I got the number of complaints for a period.
Also I alreadt made the measure for 'Complaints#'
What I now want is following:
For New Complaints I already have a measure which I think is ok.
Because dimDate is linked to CreateDate (which are the new complaints) this would be straight forward.
Now with Closed Complaints I am having a hard time.
There is a closedate and in the data I also made a calculated colume Closure Month with the format of Period from dimDate.
So for the field dimDate[Period] I want all the complaint (id's) that have a Closure Month equal to the dimDate[Period].
For line period 06-2023 I want to find all the complaint id's that have cloure month 06-2023 (a closedate in 06-2023)
A third would then be the number of open complaints in a given period.
So again for 06-2023 the number or complaints for which the closure date is not filled or higher than 06-2023.
Would anybody know how to guide me in this with help of this sample file??:
https://drive.google.com/file/d/1ag2P5mQd1SEaoSPsQXAuQubloYRQkcaQ/view?usp=sharing
Sorry to bother you again but I found now that New and Closed are ok but Open is not.
And I found out now where it is going wrong.
Compare against excel for the month of June 23 :
Can clearly see here that in Power BI the complaints that have been create before the month of June are not taken into the count for still being open.
As long as they are created in June and where closed after June they will be in.
This is the measure I used for it but guess it needs to be more complicated :
So the number should not be 36 but 39 because of 3 complaint openend before June 23 and closed after June 23.
Well this is great @Adescrit !
Did not know you could have 2 links and that a dotted link still does 'something'.
Also the use of USERELATIONSHIP in the CALCULATE function is new for me.
Thanks for teaching me 😄
Little thing:
For this new closed complaints also a blank period comes in.
When I only have NewComplaints this does not happen.
Any idea what could cause this?
Hi @rpinxt
Glad to help. That functionality blew my mind when I discovered it too!
The numbers reported against a blank period - that could be complaints that were closed on a date that doesn't exist in your date dimension table. So either extend the range of dates within that table or filter out the blanks if you aren't interested in that date range.
Ok thanks @Adescrit .
Yes it are of course complaints that have no close date yet.
Not sure how to filter that without impacting the new complaints but will find a way.
Would you have to suggestion for me about the calculation of the open complaints?
It is a bit more complicated then new and closed I think.
And mind you the 'Status' field has not really something to do with all this, it is a different kind of status.
So for a given period something is open when the createdate is smaller or equal to the dimdate period and the closeddate is larger then the dimdate period.
But how would you do that with all these userelationship in the calculate function?
Just to confirm, do you mean that a complaint is open if the Created Date is before the current period, and if the Close Date is after the current period?
And Closed complaints would be if they have a closedDate before or equal to the current period?
Yes @Adescrit
For instance if a complaint has create date in 05-2023 and no close date in 06-2023 it is open in 06-2023.
And if there is a close date in 06-2023 (or before) it counts as a closed complaint.
And in the example you provided, would the complaint that has a create date in 05-2023 and no close date in 06-2023 also be classed as open for all future periods, e.g. 07-2023, 08-2023 etc. Or is only classed as "open" in the period after its created date?
Sorry just trying to make sure I understand fully before providing a suggestion.
That is correct @Adescrit
As long as there is no close date it is open.
As soon as it is created it is new and it is open.
When there is a close date it is closed.
Just thinking now if 1 complaint should be counted as open and as new......
Maybe both options could be adressed?
So closed is closed (when in that period!)
But created means in case 1 that the date it is created for that period it is New and Open.
And case 2 that the date it is created for that period it is New. And only in the next period it is not new anymore but open.
Just hoping I don't make it more confusing now...
Hi @rpinxt
This might require some rework to fit within your actual model, but this is what I've got and it seems to work:
Open Complaints =
SUMX (
// Create an aggregation table that counts the number of open complaints per period
SUMMARIZE (
dimDate,
dimDate[Period],
"Open Complaints",
CALCULATE (
DISTINCTCOUNT ( Complaints[id] ),
FILTER (
ALL ( Complaints ) // Created Date is before the start of the month
,
Complaints[CreatedDate]
< EOMONTH ( MIN ( dimDate[Date] ), -1 ) + 1 // CloseDate is after the end of the month, or null
&& (
Complaints[CloseDate] > EOMONTH ( MIN ( dimDate[Date] ), 0 )
|| ISBLANK ( Complaints[CloseDate] )
)
)
)
),
[Open Complaints]
)
Thanks for this extensive answer @Adescrit !
Unfortunately there was some fields that I had to change in order to work in the provided sample.
I'm afraid I did not do it correct because it renders no output in my sample file.
Made this :
Hi @rpinxt did you create a measure or a column? Just in case, it should be a measure.
I've renamed the table to Case to make it easier to transfer, but this code does work for me, and returns the number of open complaints in that period, i.e. cases with a created date in the prior to the start of the period and close date that is either null or after the end of the period.
Open Complaints =
SUMX(
// Create an aggregation table that counts the number of open complaints per period
SUMMARIZE( dimDate,
dimDate[Period],
"Open Complaints",
CALCULATE( DISTINCTCOUNT( 'Case'[id] )
, FILTER( ALL( 'Case' )
// Created Date is before the start of the month
, 'Case'[CreatedDate] < EOMONTH( MIN(dimDate[Date] ), -1) + 1
// CloseDate is after the end of the month, or null
&& ( 'Case'[CloseDate] > EOMONTH( MIN(dimDate[Date] ), 0) || ISBLANK( 'Case'[CloseDate] ) )
)
)
), [Open Complaints] )
Yes this is now also working for me.
However the open amounts are too high:
Should more look like :
Where here in the excel formula Colum Y and Column Z are Initiation Month (Y) and Closure Month (Z).
These 2 columns are also present in the sample file as calculate columns (based on create date and closeddate)
Ps: can forget about column C in the excel formula. There is only 1 division.
Hi @rpinxt
To start with, I would suggest creating a relationship between ClosedDate in your Case table and Date in the dimDate table. You do not need to make any changes to the existing relationship between CreatedDate and Date. An inactive relationship will be created, represented by a dotted line between the two tables.
You can then create a measure for Closed Complaints as follows:
Closed Complaints =
CALCULATE( [#Complaints],
USERELATIONSHIP( Case[ClosedDate], dimDate[Date]) )
NOTE: You may need to add an additional condition to this measure to filter for only "Closed" complaints. Perhaps like the following but you would need to amend accordingly:
Closed Complaints =
CALCULATE( [#Complaints],
Case[Status] = "Closed",
USERELATIONSHIP( Case[ClosedDate], dimDate[Date]) )
You should be able to use the same kind of logic for Open complaints but change the status filter accordingly.
Thank you
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
User | Count |
---|---|
92 | |
91 | |
84 | |
80 | |
49 |
User | Count |
---|---|
146 | |
137 | |
109 | |
68 | |
55 |