Calculate for a periode occurences that have create date before that period

[rpinxt]

Below will be the link to the sample file.

In general I have this :

Per period (autocalendar linked to create date) I am looking for the Open Complaint. For this example June 23.

A complaint is open in June 23 when :

1) ClosedDate > dimDate (Period 06-2023)

2) CreateDate <= dimDate (Period 06-2023)

As you see at the left my measure gives me 36.

But to the right you see the details and there are 39.

The 3 missing are the ones that were created before June 2023 and where closed after June 2023.

My Measure :

Open Complaints =

    CALCULATE( [Complaints#],

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

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

    )

How could I ammend this formula to work as expected?

The sample file :

https://drive.google.com/file/d/1byGzm_MNon-cYfsJ5huWuu70OMa3u3bW/view?usp=sharing

[rpinxt]

Also if somebody knows the TOTALYTD formula will not work for this please let me know.

No use for me to try and make it work if it can't.

If you think you can do it with another Calculate formula don't hesitate to make the suggestion 🙂

[rpinxt]

Ok apprantly harder than I thought.

But I made some progress. As I realized I had to see the open complaints as a YTD calculation.

So I used this formula :

YTD Open =

TOTALYTD([Complaints#],

dimDate[Date],

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

)

And look at this....almost there :

38....almost....

Only 1 (litteraly) that I am missing now is the one that was created in 2022.

But how to change this formula so the 2022 complaint will also be counted.

"'Case'[ClosedDate] > MAX(dimDate[Date]" is the filter and seems I cannot put in another filter for something like ALL.

[rpinxt]

Hope you all can access the sample file.

If not please somebody let me know.