Power BI Desktop - Complex date grouping in bar chart request and alternate X axis formatting

[Krc721]

I have a reporting ask where we report data back to 2019 in a quarterly basis, this is making the graphs way to large.  We are moving to Power BI and i am new to Power BI.   I have rebuilt the views from excel and they work fine except there are two changes I am being asked to make.  

1) Can we have one column for each year 2019-22 for year end values and then current year be quarterly?

1a) Can we do the #1 scenario but have every month from current year quarter end report monthly.

1c) As an alternate to 1-1a, is there a way to do a rolling series of 8 quarters w/out having to manually adjust and republish each month, we are trying to be as hands-off on the dashboards as possible.

2) We have dates for the x-axis labels (quarter or month name), the ask is to format those numeric values into text for display.  I used a DAX calendar and filters to select only quarter ends months; however, if I use a formula to calculate MMM-YY it sorts alpha (as expected).  So I have been asked can we simply reformat the date numeric values to some sort of text.  

We have power bi desktop, waiting on a power bi premium license to be ordered.

thanks

[Krc721]

Thank you so much. I have had Power BI w/only some video self training for about 3 weeks now so I did a little DAX but not a lot so the guidance is definitely appreciated. Is there any resources you can suggest that can provide a beginner a rapid onboarding?    

[123abc · Accepted solution]

It sounds like you have a few specific requirements for your Power BI report. I'll address each of your requests:

1. Quarterly Data for 2019-2022 and Monthly Data for the Current Year: You can achieve this by creating a new column in your date table that categorizes each date as "Year End" or "Monthly" based on your criteria. Here's a simplified example of the DAX formula:

Date Category = IF(YEAR('DateTable'[Date]) = 2022, "Monthly", "Year End")

1. Then, you can use this new column to control how your data is visualized in your report.

1a) Reporting Monthly Data for the Current Year's Quarters: You can create a separate visual that shows monthly data for the current year's quarters. Use the "Date Category" column mentioned above to filter your data accordingly. This way, you can have both quarterly and monthly views in your report.

1c) Rolling Series of 8 Quarters: To create a rolling series of 8 quarters without manual adjustments, you can create a dynamic date table. Power BI allows you to create date tables using DAX functions like CALENDAR or CALENDARAUTO. You can also use a combination of DAX functions to determine the start and end dates for your rolling 8-quarter period. Here's a simplified example:

Start Date = TODAY() - (8 * 91) // Assuming a quarter is roughly 91 days
End Date = TODAY()

Then, use these calculated dates in your visuals to display data for the rolling 8-quarter period.

1. Formatting Numeric Date Values as Text: To format your numeric date values as text, you can use the "Text" data type in Power BI. You can create a new column in your date table that converts the date values into the desired text format. Here's an example:

Formatted Date = FORMAT('DateTable'[Date], "MMM-YY")

1. This will create a new column with the date values in the format "MMM-YY," which will be sorted correctly.

Remember that Power BI Desktop is a powerful tool for data transformation and visualization, and DAX expressions can help you achieve complex requirements like the ones you mentioned. Once you have your Power BI Premium license, you can also publish your reports to the Power BI Service for sharing and collaboration.