Create status labels per month to report on timeliness of complaints using dates conditions

[Anonymous]

Dear Power BI community!

I have a pretty complex situation that has been keeping me busy for weeks, after which I decided I can't find a good solution. The below situation is somewhat simplified.

• I support a department that processes complaints and want to make sure they are processed within 20 days. To report on this, I am developing a dashboard with a monthly KPI: Timeliness %, being the monthly number of complaints processed on time, divided by the total monthly number of complaints processed.
• My dataset (COMPLAINTS) consists of 5 attributes:
  • [ComplaintId]
  • [DateStart]
  • [DateDue] defined as DateStart + 20 days
  • [DateEnd]
  • [TeamId] I want to be able to slice the KPI on team level to enable managers to manage their teams.

The issue is as follows:

• I have created a new data table (DataTable) in Power Query, listing every first day of the month (column [Date], e.g. 1-1-2023, 1-2-2023, etc)
• For each date, I want to count the complaints in their statuses based on the first table's data. 
  • [ClosedOnTime]: number of complaints on date, where [Date] >= [DateProcessed] AND < [DateDue] 
  • [ClosedOverdue]: number of complaints on date, where [Date] >= [DateProcessed] AND < [DateDue] 
    • The closed complaints will remain in the data, therefore it is cumulative over time.
    • [OpenOnTime]: number of complaints on date, where [Date] >= [DateStart] AND [Date] < [DateProcessed] AND <= [DateDue]
    • [OpenOverdue]: number of complaints on date, where [Date] >= [DateStart] AND [Date] < [DateProcessed] AND <= [DateDue] 

Based on this, I would like to calculate the monthly number of processed complaints as follows:

• ProcessedComplaints-Ontime: [ClosedOnTime] at T=0, minus [ClosedOnTime] at T-1
• ProcessedComplaints-Total: [ClosedOnTime] + [ClosedOverdue] at T=0, minus [ClosedOnTime] + [ClosedOverdue] at T-1

Lastly, I'd like to create an overview where the KPI is presented on a monthly basis (timeliness% January, timeliness% February, etc) for the last 8 or so months, preferably following the current date using relative date being the last 8 months (i.e. when we reach the new month, the previous month is automatically added and the oldest month is dropped).

Can someone please help me how to approach this and what the correct coding is?

For example, for the column OpenOnTime, I created the following, which doesn't seem to work:

OpenOnTime =

VAR Dates =

    SELECTEDVALUE(DataTable[Date])

RETURN

       CALCULATE(

          COUNTX(

             'data complaints',

             'data complaints'[ComplaintId]),

                    FIRSTDATE(DataTable[Date]),

                         'data complaints'[DateStart]                 <=                 Dates &&

                         ('data complaints'[DateEnd]                  >                   Dates || 'data complaints'[DateEnd] = BLANK()) &&

                         'data complaints'[DateDue]                   >                   Dates)

          )

Any help would be MUCH appreciated. Thank you in advance!

[Anonymous · Accepted solution]

HI @Anonymous,

You can extract the current date from date table and use this as condition to filter and calculate with 'fact' table records:

OpenOnTime =
VAR currDate =
    MAX ( DataTable[Date] )
RETURN
    CALCULATE (
        COUNT ( 'data complaints'[ComplaintId] ),
        FILTER (
            ALLSELECTED ( 'data complaints' ),
            AND (
                'data complaints'[DateStart] <= currDate,
                OR (
                    'data complaints'[DateEnd] > currDate,
                    'data complaints'[DateEnd] = BLANK ()
                )
                    && 'data complaints'[DateDue] > currDate
            )
        )
    )

Regards,

Xiaoxin Sheng