Closed and opened in same graph

 You can create this column bar graph in Power BI by following these steps:

1. Load Your Data:

   • Ensure your dataset containing RTSK Number, Date opened at, and Date closed at is loaded into Power BI.

2. Create Date Columns:

   • In Power Query Editor, create new columns to extract the month from Date opened at and Date closed at.

   For Date opened at:

   • Add a new custom column and use the following formula to extract the month:

     m

     Date.ToText([Date opened at], "MMM")
     

   • Rename this column to Opened Month.

   For Date closed at:

   • Add a new custom column and use the following formula to extract the month:

     m

     Date.ToText([Date closed at], "MMM")
     

   • Rename this column to Closed Month.

3. Create a Date Dimension Table:

   • It's a good practice to have a date dimension table to manage your date fields effectively.

4. Count RTSK Numbers by Month:

   • Back in Power BI Desktop, create two measures to count the number of RTSK Numbers opened and closed each month.

   Measure for Count of RTSK Numbers Opened:

   dax

   Count RTSK Opened = COUNTROWS(FILTER(YourTable, MONTH(YourTable[Date opened at]) = MONTH(YourDateTable[Date]) && YEAR(YourTable[Date opened at]) = YEAR(YourDateTable[Date])))
   

   Measure for Count of RTSK Numbers Closed:

   dax

   Count RTSK Closed = COUNTROWS(FILTER(YourTable, MONTH(YourTable[Date closed at]) = MONTH(YourDateTable[Date]) && YEAR(YourTable[Date closed at]) = YEAR(YourDateTable[Date])))
   

5. Create the Bar Chart:

   • In Power BI Desktop, create a clustered column chart.

   • Drag the Month field (from your Date Dimension Table) to the X-axis.

   • Drag the Count RTSK Opened measure to the Y-axis.

   • Drag the Count RTSK Closed measure to the Y-axis.

   • Ensure you have the months sorted correctly (Jan, Feb, Mar, etc.).

6. Customize the Visual:

   • Customize the visual as needed (e.g., colors, labels) to ensure it clearly represents the data.

Following these steps will help you create a column bar graph that shows the count of how many RTSK Numbers were opened and closed each month.

If you need more detailed instructions or have any additional questions, feel free to ask!

How to create Date Dimension Table

Creating a Date Dimension Table (also known as a Date Table or Calendar Table) in Power BI is essential for effective time-based analysis. Here’s how you can create one:

Using DAX in Power BI Desktop

1. Open Power BI Desktop:

   • Make sure your data is already loaded into Power BI.

2. Create a New Table:

   • Go to the Modeling tab and click on New Table.

3. Enter the DAX Formula:

   • In the formula bar, enter the following DAX expression to create your Date Dimension Table:

     dax

     DateTable = 
     ADDCOLUMNS (
         CALENDAR (DATE(2020, 1, 1), DATE(2030, 12, 31)),
         "Year", YEAR([Date]),
         "Month Number", MONTH([Date]),
         "Month Name", FORMAT([Date], "MMM"),
         "Quarter", "Q" & QUARTER([Date]),
         "Weekday", FORMAT([Date], "dddd"),
         "Weekday Number", WEEKDAY([Date], 2)
     )
     

   • Adjust the CALENDAR function’s start and end dates (e.g., DATE(2020, 1, 1) to DATE(2030, 12, 31)) to match your data range.

Explanation:

• CALENDAR: This function creates a list of dates between the specified start and end dates.

• ADDCOLUMNS: This function adds new calculated columns to the table.

• Year, Month Number, Month Name, Quarter, Weekday, Weekday Number: These columns provide various date components that are helpful for time-based analysis.

Connecting Date Dimension Table to Your Data

1. Manage Relationships:

   • Go to the Model view in Power BI Desktop.

   • Drag the Date column from your Date Dimension Table to the corresponding date columns in your fact tables (e.g., Date opened at and Date closed at).

Sorting Months Correctly

1. Sort Month Names by Month Number:

   • In the Data view, select the Month Name column in your Date Dimension Table.

   • Go to the Modeling tab and select Sort by Column, then choose Month Number.

By following these steps, you’ll create a Date Dimension Table that can be used for effective time-based analysis in your reports. Let me know if you need any further assistance or have more questions!

Is there any possibility to extract month and convert to text within DAX query?

Absolutely! You can extract the month from a date and convert it to text within a DAX query. This is very useful for creating columns or measures that display the month in a textual format (e.g., "Jan", "Feb", etc.).

Here's how you can do it using DAX:

Creating a Calculated Column

1. Open Power BI Desktop.

2. Go to Data View.

3. Select the Table where your date column is located.

4. Create a New Column by clicking on Modeling > New Column.

5. Enter the DAX Formula:

dax

MonthText = FORMAT(YourTable[Date], "MMM")

• Replace YourTable with the name of your table.

• Replace [Date] with the name of your date column.

Explanation:

• FORMAT(YourTable[Date], "MMM"): The FORMAT function converts the date to a textual representation of the month. "MMM" specifies the format as the abbreviated month name (e.g., "Jan", "Feb").

Creating a Measure

If you prefer to create a measure instead of a column, you can follow these steps:

1. Go to Data View or Report View.

2. Select the Table where your date column is located.

3. Create a New Measure by clicking on Modeling > New Measure.

4. Enter the DAX Formula:

dax

MonthTextMeasure = FORMAT(SELECTEDVALUE(YourTable[Date]), "MMM")

• Replace YourTable with the name of your table.

• Replace [Date] with the name of your date column.

Explanation:

• FORMAT(SELECTEDVALUE(YourTable[Date]), "MMM"): The FORMAT function works in the same way, and SELECTEDVALUE ensures that the measure correctly handles the current context.

Using in Visualizations

1. Create a Visual:

   • For example, a bar chart with the month on the X-axis and count of RTSK Number on the Y-axis.

2. Drag the New Column/Measure to the Visual:

   • Use the MonthText column or MonthTextMeasure measure for the X-axis.

   • Use RTSK Number for the Y-axis.