Get data per period based on an autocalendar period

[rpinxt]

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

[rpinxt]

@Adescrit ,

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 :

Open Complaints =

    CALCULATE( [Complaints#],

     'Case'[CreatedDate] <= MAX(dimDate[Date]) &&

     'Case'[ClosedDate] > MAX(dimDate[Date])

    )

So think the filtercontext here is only looking for the period (being June) and does not look before.

So the number should not be 36 but 39 because of 3 complaint openend before June 23 and closed after June 23.

[rpinxt]

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?

[Adescrit]

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.

[rpinxt]

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?

[Adescrit]

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?

[rpinxt]

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.

[Adescrit]

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.

[rpinxt]

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

[Adescrit]

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

[rpinxt]

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 :

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'[Id] ) // Created Date is before the start of the month

                    ,

                    MAX('Case'[CreatedDate])

                        < EOMONTH ( MIN ( dimDate[Date] ), -1 ) + 1 // CloseDate is after the end of the month, or null

                        && (

                            MAX('Case'[ClosedDate]) > EOMONTH ( MIN ( dimDate[Date] ), 0 )

                                || ISBLANK ( MAX('Case'[ClosedDate]) )

                        )

                )

            )

    ),

    [Open Complaints]

)

First of  in the distinct count I had to put 'Case'[Id] as in the sample file you have the table 'Case' en the Id is the complaint.

In the filter you uses in ALL just 'Complaints', so changed that to also 'Case'[Id].

Then you used Create date and close date without anything else in the dax code but this would not work for me.

Had to put them inside a MAX function otherwise DAX would not recognize the create and closedate fields

Tried in the first filter to use 'Case' instead of 'Case'[Id] but then I get output for every period after the period I want and its the full number not the number of 1 period.

[Adescrit]

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

[rpinxt]

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.

[Adescrit]

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.

[thuoc-tinh-yeu]

Thank you