Pivot Table in Power BI

 Got it! You can create a new summarized table in Power BI using DAX, which can be very useful for further analysis and creating visualizations. Here's a step-by-step guide to create a new table with your specified requirements:

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

First, ensure you have a Date Dimension Table that includes a month-year column.

Step 2: Create a New Summarized Table

1. Open Power BI Desktop.

2. Go to the Modeling tab.

3. Click on New Table.

4. Enter the following DAX formula:

dax

MonthlySummaryTable = 
VAR OpenedCount = 
    SUMMARIZE(
        'RTSKData',
        'RTSKData'[RTSK Number],
        "MonthYear", FORMAT('RTSKData'[RTSK Opened At], "MMM-YYYY"),
        "OpenedCount", COUNT('RTSKData'[RTSK Number])
    )
VAR ClosedCount = 
    SUMMARIZE(
        'RTSKData',
        'RTSKData'[RTSK Number],
        "MonthYear", FORMAT('RTSKData'[RTSK Closed At], "MMM-YYYY"),
        "ClosedCount", COUNT('RTSKData'[RTSK Number])
    )
VAR CombinedTable = 
    NATURALLEFTOUTERJOIN(OpenedCount, ClosedCount)
RETURN
    ADDCOLUMNS(
        CombinedTable,
        "OpenedCount", COALESCE([OpenedCount], 0),
        "ClosedCount", COALESCE([ClosedCount], 0)
    )

Explanation:

• SUMMARIZE: Creates a summary table for both opened and closed counts, grouped by RTSK Number and formatted month-year.

• FORMAT: Converts the Date opened at and Date closed at columns into a "MMM-YYYY" format.

• NATURALLEFTOUTERJOIN: Joins the two summarized tables on the MonthYear column.

• ADDCOLUMNS: Adds columns to the combined table and ensures that any missing counts are replaced with zero using COALESCE.

This new table will have the following columns:

1. MonthYear: The month and year in "MMM-YYYY" format.

2. OpenedCount: The count of RTSK numbers opened in each month.

3. ClosedCount: The count of RTSK numbers closed in each month.

Step 3: Use the New Table for Visualizations

You can now use this new table, MonthlySummaryTable, to create various visualizations in Power BI, such as bar charts, line graphs, or any other visual that suits your analysis needs.

Feel free to reach out if you need more details or further assistance!