Chart for Selected month count and same period last year

[stribor45]

I was wondering if anyone had suggestions on how to best present the data for selected months and the period last year months.

My data goes from last year July until now and since some months don't have data yet in the table there is nothing and in the chart they show equal values. Look for example the November? any recommendations on how to show this data?

Thank you

[parry2k · Accepted solution]

@stribor45 Well when you are working on date and time, you need to have a seperate dimension for time not as part of the calendar dimension. Search on google about time dimension and you will get the solution. 

Basically, the calendar dimension is only the date data, the time dimension is only the time data and data table where the date and time will be in two different columns. This is the right approach and the best practice.

[stribor45]

@parry2k I will try that and let you know

[parry2k]

@stribor45 then it is not a calendar table, calendar time needs to have a unique date, you need to remove the time element from it and then use it. 

Check the related videos on my YT channel

Add Date Dimension
Importance of Date Dimension
Time Intelligence Playlist

[stribor45]

I think it worked (see below). I do have a couple of questions

1.  Is there a way to adjust the tooltip to show different text?  If you mouse over the August 2023 bar and the bar right next to it for the August 2022 only difference is the count but both will show August 2023 (Year Month) is always the same
2. Is there a way to show these in order? as of now August is shown before July and should be other way around.
3. Is there a way to show something when there is no data for previous or current month. As an example as of now i have no data for October 23 but I do have data for Octover 2022. Is this the best way to show if there is no data there is no bar?

Thank you 

[parry2k]

@stribor45 

1 - not as such but you can look at the report tooltip (aka custom tooltip)

2 - this is a sorting issue, you should look into sort by to sort your month/year column.

3 - totally your call what you want, I would recommend not to show previous year data if current year data is not available. 

[stribor45]

@parry2k this is what my datetime column in table #1 and table #2 looks like.. If I remove the time portion from the calendar table how wouldn't that break the relationship? Removing the time portion from Calendar and Table #1 will also not allow me to be able to count calls based on the time of the day (morning, afternoon and evening). As of right now this seems to work when presented in table visual but not in chart visul

[parry2k]

yes sir

[stribor45]

This is my Calendar table (it goes up in time by 1 min). When I try to mark it as a date table this is what i see

 

[parry2k]

@stribor45 I see the mistake, you need update your measure and also make sure your date table is marked as date table:

TOTAL PREV PERIOD = CALCULATE([TOTAL COUNT], SAMEPERIODLASTYEAR('Calendar'[Dates]))

[stribor45]

You mean mark my Calendar table as date table?

[parry2k]

Share your measure expression? Do you have date dimension in your model?

[stribor45]

I do have a calendar/date table which has a relationship with Table #1 and Table #2

TOTAL COUNT = COUNT ('Table #1'[Caller ID]) + COUNT('Table #2'[Caller ID])
TOTAL PREV PERIOD = CALCULATE([TOTAL COUNT], SAMEPERIODLASTYEAR('Calendar'[Dates].[Date]))

[parry2k]

@stribor45 you shouldn't be showing only the month name on the x-axis because you cannot tell which month of which year you are comparing, for now probably it doesn't matter but once you have 3-4 years this will be hard to read.

You should have a column with Year/Month concatenated and use that on the x-axis then you will not have this issue.

[stribor45]

I tried that but didn't work.  July, August and September should have data for this year and for the past