Show short Month Name on clustered chart when Relative date is used for CY PY

[Eliezer20101980]

I created a report to show Rolling 12 months CY, PY using SAMEPERIODLASTYEAR DAX as shown for example;

1. NewIntake = SUM(‘Customer Service Center Metrics’[New])
2. Previous = CALCULATE(‘Customer Service Center Metrics’[NewIntake],SAMEPERIODLASTYEAR(‘Date2’[Date]))

I am using Relative date in the filter section to limit it to show values in the last 12 month which gives me my 12 month each time a new month is uploaded. This works fine but I am not able to make the X-Axis show Short-Month (Jan, Feb etc.).

I would appreciate help in figuring out what am doing wrong and any suggestion to what solution will work for me.

I have attached Document With Screenshots.
Show short Month Name on clustered chart when Relative date is used for CY PY.docx (452.6 KB)

[Melissa]

Hi @Eliezer20101980,

For that you need a Month short name attribute in your Date table and a sort by column like the month number… then drag the short name to the axis.

Date = 
ADDCOLUMNS (
    CALENDARAUTO( ),
    "Month Name", FORMAT ( [Date], "mmm" ),
    "Month Number", MONTH ( [Date] )
)

.

Alternatively you can use this M code for an extended date table

Extended Date Table (Power Query M function)

Hi everyone, To make it easier to find I’ve placed the updated Power Query M code function for the extended Date Table / Dates Table in the category where it belongs… I hope you’ll find it useful and if there are any questions, just reach out and let me know! // August 2022 There has been an overhaul of the M code from when it was initially shared. This affects column names and values. In the Analyst Hub (follow this link) you can copy a version of Extended Date table which returns a r…

[EnterpriseDNA]

Hi @Eliezer20101980, Ihope that you are having a great experience using the Support Forum so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

[Eliezer20101980]

Thank you @Melissa
I added the Month short attribute in my Date table and am still not getting the desired result. I have attached the PBIX File for a more detailed view of the dataset. I also created a another Date Table using the M Query you put up in your reply.

CSM PBIX Latest.pbix (158.2 KB)

Thank you

[Harsh]

Hello @Eliezer20101980,

Thank You for posting your query onto the Forum and for providing the working of the PBIX file.

Now, I removed the old Date table and retained the new Date table which you’ve added into your model using @Melissa’s M code. So I observed that there was a bi-directional filter between the Fact and Dimension table which has been converted to the uni-directional flowing from “Date” table to “Customer Service Center Metrics” table. Below is the article provided for the reference about “Why Bi-Directional Filter Is Not Recommended?”

Now, corrected the formula for “Avg Aging Days PY” since it was referencing the old date table. Below is the revised formula provided for the reference -

Avg Aging Days PY = 
CALCULATE( SUM( 'Customer Service Center Metrics'[Average Aging in Days] ) , 
    SAMEPERIODLASTYEAR( 'Date Table'[Date] ) )

Now, drag the “MonthShortName” into the Bar Chart and sort it using the “MonthOfYear” column. So that short month name are properly arranged in the month order.

And lastly, add the “Date” column into the FILTER PANE and select the option as “Relative Date”. Below is the screenshot provided for the reference -

And now, you’ll have the chart showing the last 12 months with the short month name. Below is the screenshot provided for the reference -

I’m also attaching the PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

CSM PBIX Latest - Harsh.pbix (132.4 KB)

Bidirectional relationships and ambiguity in DAX - SQLBI

[Eliezer20101980]

@Harsh. Thank you so much for helping me understand the bi-directional filter and resolving this for me. I have gain more knowledge in what would help me for the future on the need for a good date table.

I appreciate it.

[Harsh]

Hello @Eliezer20101980,

You’re Welcome.

I’m glad that I was able to assist you and you found the solution useful.

Thanks and Warm Regards,
Harsh

[EnterpriseDNA]

Hi @Eliezer20101980, did the response provided by @Harsh and @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

[Eliezer20101980]

Hello @EnterpriseDNA, Yes the response provided by @Harsh and @Melissa helped me solve my query. I have marked as solution the answer that helped to solve my query.

Thank you.

[Eliezer20101980]

@Harsh , @Melissa and @EnterpriseDNA . Is there a way to have the legend show the years only with same color for the bars for Current year and Previous Year ?

I have an attachment screen shot example of what am referring to.

Thank You.

[Harsh]

Hello @Eliezer20101980,

Please create a new post since the query is different is the original one and start a topic for more visibility.

Thanks and Warm Regards,
Harsh

[Eliezer20101980]

Thank you @Harsh. I will create a new post. Meanwhile this Query is a continuation of the original solution so should I attach the files from the last one to the new query?

Regards

[Harsh]

Hello @Eliezer20101980,

Sure you can attach the files, if they’re relevant the new post and want to see the solution in that file.

Thanks and Warm Regards,
Harsh