Calculate Outstanding RTSK

 

Measure 1: Count of RTSK opened in a month but not closed in the same month

First, we'll create a calculated column to flag if an RTSK was not closed in the same month it was opened:

1. Create a Calculated Column in the RTSK_opened table:

   dax

   RTSK_NotClosedSameMonth = 
   VAR ClosedDate = 
       CALCULATE(
           MAX('RTSK_closed'[RTSK Closed Date]),
           'RTSK_closed'[RTSK Number] = 'RTSK_opened'[RTSK Number]
       )
   RETURN
   IF(
       YEAR('RTSK_opened'[RTSK Opened Date]) <> YEAR(ClosedDate) || 
       MONTH('RTSK_opened'[RTSK Opened Date]) <> MONTH(ClosedDate),
       TRUE(),
       FALSE()
   )
   

2. Create a New Measure for RTSKs opened in a month but not closed in the same month:

   dax

   RTSKOpenedNotClosedSameMonth = 
   CALCULATE(
       DISTINCTCOUNT('RTSK_opened'[RTSK Number]),
       'RTSK_opened'[RTSK_NotClosedSameMonth] = TRUE()
   )
   

Measure 2: Count of RTSK closed in a month but not opened in the same month

Similarly, we'll create a calculated column to flag if an RTSK was not opened in the same month it was closed:

1. Create a Calculated Column in the RTSK_closed table:

   dax

   RTSK_NotOpenedSameMonth = 
   VAR OpenedDate = 
       CALCULATE(
           MAX('RTSK_opened'[RTSK Opened Date]),
           'RTSK_opened'[RTSK Number] = 'RTSK_closed'[RTSK Number]
       )
   RETURN
   IF(
       YEAR('RTSK_closed'[RTSK Closed Date]) <> YEAR(OpenedDate) || 
       MONTH('RTSK_closed'[RTSK Closed Date]) <> MONTH(OpenedDate),
       TRUE(),
       FALSE()
   )
   

2. Create a New Measure for RTSKs closed in a month but not opened in the same month:

   dax

   RTSKClosedNotOpenedSameMonth = 
   CALCULATE(
       DISTINCTCOUNT('RTSK_closed'[RTSK Number]),
       'RTSK_closed'[RTSK_NotOpenedSameMonth] = TRUE()
   )
   

Plotting the Bar Graphs

1. Go to the Report view in Power BI Desktop.

2. Select the Bar Chart visual from the Visualizations pane.

Plot 1: RTSK Opened in a Month but Not Closed in the Same Month

1. Drag and Drop the YearMonth column from the Date table to the Axis field.

2. Drag and Drop the RTSKOpenedNotClosedSameMonth measure to the Values field.

3. Customize the bar chart as needed (e.g., add titles, adjust colors).

Plot 2: RTSK Closed in a Month but Not Opened in the Same Month

1. Select another Bar Chart visual from the Visualizations pane.

2. Drag and Drop the YearMonth column from the Date table to the Axis field.

3. Drag and Drop the RTSKClosedNotOpenedSameMonth measure to the Values field.

4. Customize the bar chart as needed.