Grouping Text by Month

[tristanweeks47]

Hello, 

I am trying to group closed accounts by month. The data i have is account type, weather the account is closed or not. So i have filtered this to be closed. Then the other data i have is date it was closed, i have tried to filter this to just months (last 6 calendar months) however the data is showing for each day an account was closed as seen in the image below. I am using DirectQuery and have tried multiple power querys to group the closed accounts by month, but dont seem to be getting anywhere. Please see below screenshots and any help would be greatly appreciatted. Any other info required to potentially help me can be provided.

Thanks,

Tristan 

[AjithPrasath]

Hi, try inserting the month column created in x axis of the chart. If you can able to post ss it will be useful  

[tristanweeks47]

So have managed to do what you suggested, however the next issue is now months 11 and 12 showing before month 1. I would also rather in be in the format MMM so Jan, Feb, Mar, ect. 

[AjithPrasath]

Hi,

  Try creating new column 

Month name=format(date(1,[month],1),"MMM")

The above format will contain the name of the month .try inserting that in the x axis. If order is not correct , try to sort the month name column with month.

Ps-if answer is working please accept this as the solution.

[tristanweeks47]

Using DirectQuery & PowerQuery so the function FORMAT wont be recognised

[AjithPrasath]

Then you can create new date table containing month number and month name column with matching value. In modelling tab create the relationship using the month number of new table with the existing table month .Now you can use month name in the chart.

[AjithPrasath]

if you are using DirectQuery mode, the built-in date hierarchy will not be available. In this case, you will need to create a custom date hierarchy manually. To do this, follow these steps:

1. In the Fields pane, right-click the date column and select New Column.
2. In the New Column dialog box, enter a name for the new column and select Date/Time as the data type.
3. In the Formula bar, type the following formula:

Code snippet

=YEAR([DateColumn])

4.Click OK.

Repeat steps 1-4 to create columns for the Quarter, Month, and Day.

Once you have created the columns for the Year, Quarter, Month, and Day, you can create a custom date hierarchy by following these steps:

1. In the Fields pane, right-click the original Date column and select New Hierarchy.
2. In the Create Hierarchy dialog box, select the Year, Quarter, Month, and Day columns.
3. Click OK.

The custom date hierarchy will be added to the Fields pane. You can then use the hierarchy in your visuals to filter and slice your data by date.

[tristanweeks47]

I managed to do this but still came accross the same issue unfortunately

[AjithPrasath]

To create a date hierarchy in Power BI, you can follow these steps:

1. Ensure that your date column is of the "Date/Time" data type in Power BI. If it's not, you may need to change the data type of your column to "Date/Time" using the Power Query Editor.

2. In the Power BI Desktop report view, select the table or visual that contains your date column.

3. On the right side pane, navigate to the "Fields" section. Locate your date column under the corresponding table.

4. Click on the drop-down arrow next to your date column. You will see a list of options.

5. From the options, select "Hierarchy" and then choose "Date Hierarchy."

6. Power BI will automatically create a date hierarchy for your selected date column. By default, it includes Year, Quarter, Month, and Day levels.

7. You can expand or collapse the hierarchy levels by clicking on the arrows next to each level.

8. To use the date hierarchy in your visuals, drag and drop it onto the axis or filter area of your visual.

[tristanweeks47]

It would appear i cant use this option as the data is live from dynamics, im using directquery. The drop down arrow next to the date column im using only shows add hierarchy rather than add date hierarchy. Is there a way i can do this in PowerQuery?

[AjithPrasath]

Hi, if the data type of date_closed is date, then  create the hierarchy for the date_closed and select only the month from the hierarchy and insert in the X-axis of the graph. Please let me know if you have any issue

[tristanweeks47]

Hello, seem to be struggling with that to be honest. Tried following a guide on how to create a date hierarchy and it doesnt seem to be working