Missing data for the last month when Time dimension is used in Column

Hi, I have a report to calculate the number of support tickets created, resolved and open for the current year grouped by Product Component (a custom field). I have the Time dimension added as a Column and selected ‘Current Month’ as an additional member. The report is missing data for the month of April 2022. Please refer to the snapshot below. Are you able to help figure out why is this happening? Thank you.

Hi @Rahul_Aurora,

Would it be possible to know what other Time member you have selected please? And if it’s a custom one, could you share the formula, this might give some clues as to the cause of you issue.

Thanks,
Marilou

Marilou,
Please view the attached snapshot for the selections. There is no custom Time member implemented.

Hi @Rahul_Aurora,

I think you are only showing Q1 for 2022 and current month.
When you built the report initially, I think you selected current year, since you have no data before Jan 2022. It’s also possible you used All Times and your data starts in Jan 2022. In either case, I think the process and result would have been the same.

Here are the steps I think you took (At least, those are the only steps I found to reproduce your issue)
1- In Time, deselect All Times, select Current Year and expand it
image

2- Expand 2022, and then expand Q1 2022

3- in Time, deselect Current Year

4- Left click on 2022 and select Remove
image

5- Repeat with Q1, Q2, Q3, Q4
image

6- In Time, select current Month

This is how I think you ended up with only Jan, Feb, Mar and May.

To solve this, I think you could create a Calculated Member in Time that display Current Year in Months instead of Quarters

Aggregate(
Filter(
  [Time].[Month].Members,
  [Time].CurrentMember.Parent.Parent is
  [Time].[Year].CurrentDateMember
  ))

And then, you could use “Hide empty Columns” to hide the futur months.

I hope this helps,
Have a good day,
Marilou

1 Like

Thanks for the response Marilou. I followed your recommended approach and created a custom Calculated Member. I see the data on a monthly basis now. One thing that is not clear to me is why do I see the data for the month of June? See snapshot attached below:

@Marilou just following up on my latest question. Please let me know. Thank you.

Hi @Rahul_Aurora,

I didn’t find any perfect solution for the future months being displayed.
What you could do is put your product components in Column and Time in Row.
Once time is in Row, you can right click any displayed time member, select Filter Time, then ≤. In the pop up box you can just write “Now” and this will filter your time up to the present day.

I don’t know of a way to do the equivalent while Time is in Column :frowning:

I’ll keep thinking about it and if I find something I’ll be sure to send it your way.

Have a good day,
Marilou

Hi @Rahul_Aurora , @Marilou
Kudos for the solution!

To show only months of the year up today, you may change the “Current year” calculated member (or, better, create a new one) to show only past and current months of the year:

Aggregate(
PeriodsToDate([Time].[Year], [Time].[Month].CurrentDateMember))

Use this calculated member instead of the “Current year” calculated member in your solution.

Best,
Ilze, support@eazybi.com

2 Likes

Hi @ilze.leite The solution works like a charm! Thank you very much for your help. One question. I created a new member as you suggested. When I expand the Aggregate column, the order is not consistent. On one report the 2022AggregateEPS column shows up on the left and on the other it shows on the right. Please view the attached snapshots. How can I control the order-left or right? Thanks.
FYI @Marilou