Outstanding Tasks count

 Yes, it’s possible to create a measure in Power BI to show how many RTSK were open by the last day of each month. Here’s how you can do it:

Step 1: Create a Date Dimension Table (if not already created)

First, ensure you have a Date Dimension Table as described earlier. This table should have continuous dates spanning the period you're analyzing.

Step 2: Create the Measure

1. Open Power BI Desktop.

2. Go to the Modeling tab.

3. Create a New Measure with the following DAX formula:

dax

OpenRTSK_EndOfMonth = 
VAR LastDayOfMonth = 
    EOMONTH(MAX('DateTable'[Date]), 0)
VAR OpenRTSK = 
    CALCULATE(
        COUNTROWS('RTSKData'),
        'RTSKData'[RTSK Opened At] <= LastDayOfMonth && 
        (ISBLANK('RTSKData'[RTSK Closed At]) || 'RTSKData'[RTSK Closed At] > LastDayOfMonth)
    )
RETURN
    OpenRTSK

Explanation:

• EOMONTH(MAX('DateTable'[Date]), 0): Finds the last day of the current month in context.

• CALCULATE: Computes the number of RTSK rows where the RTSK Opened At date is on or before the last day of the month and the RTSK Closed At date is either blank or after the last day of the month.

  • COUNTROWS('RTSKData'): Counts the rows that meet the conditions.

  • ISBLANK('RTSKData'[RTSK Closed At]): Ensures that open RTSKs are counted even if they are not closed yet.

Step 3: Create the Graph

1. Create a Bar Chart:

   • Go to the Report view.

   • Select the Clustered Column Chart visual.

2. Add Data to the Chart:

   • Drag the Month column from the DateTable to the X-axis.

   • Drag the OpenRTSK_EndOfMonth measure to the Y-axis.

3. Sort the Months:

   • Make sure the months are sorted in the correct order (Jan, Feb, Mar, etc.). You can sort the Month column by Month Number in the Modeling tab.

By following these steps, you will have a measure that shows the number of RTSKs that were open by the last day of each month, which can be visualized in a graph.

If you need further assistance or have more questions, feel free to ask!