Cumulative Bar Chart - How to Hide Months Without Affecting Values

[rayinOz]

Hello Community!

In my month to month cumulative bar chart, i'm wondering how to hide earlier months (to fucus more on recent months and keep the bar chart smaller) without affecting the numbers. Whenever I filter using the visual fiter to hide months, it also adjusts the values... which I don't want.

thoughts?

[v-huizhn-msft]

Hi @rayinOz,

Please try the solution @Sean posted, and mark the reply as answer if you have resolved your issue. If your issue still exist, please feel free to ask.

Best Regards,
Angelia

[Sean]

Plot your CalendarTable[Date] on the X-Axis while using your Table [Date] in your Cumulative Total Measure!

(and also use the CalendarTable[Date] for the Slicers and in Visual Level Filters, etc...)

[rayinOz]

Sean,

I'm a novice so that didn't quite make sense to me. I do have a date table and measures within my file (see below chart image). However i was able to change the x-axis formatting start date via the formatting panel. Visually it made the change I wanted without affecting the data. Unless it causes other changes that I haven't been able to notice.

 Take a look at the fields in my pbi file.

and my bar chart visualisations

 What do you think?

[Anonymous · Accepted solution]

Hi @rayinOz

I go about doing this as follows.

I am assuming you have a calendar table which is based on the min to max dates of your main fact table.

Try the following

1.  Create a column called Monthnumber in your calendar table

    MonthNumber = MONTH([Date])

2.  Create a column called MonthSequentialNumber in your calendar table

     MonthSequentialNumber = year([Date])*12 + Calendar[MonthNumber] – 1

3.  Create a measure called 

      MaxDate = Max('Calendar'[Date])

4. Create a column called Show  in Calendar table 

                  Show = If ( [MonthSequentialNumber] >= [CurMonthSerialNumber] -11 &&
                                                     [MonthSequentialNumber] <= [CurMonthSerialNumber] ,
                                                                     1,
                                                                            0

                                                         )

What this does for each row in calendar table it checks whether the month sequential number is less than 12 months including the max current month. If so set it as 1 else 0.

I have used the last 12 months to be displayed including the current month in my case. You may change this value 11 to display as many months backwards you want to show.

5. In the visual filters for the chart set Show equals 1.

If this resolves your issue accept this as a solution and also give KUDOS.

Cheers

CheenuSing

[Anonymous]

Hello,

I am having the same issue and trying to implement your solution. Can you please specify what represents [CurMonthSerialNumber]  in this calculation?

Show = If ( [MonthSequentialNumber] >= [CurMonthSerialNumber] -11 &&
                                                     [MonthSequentialNumber] <= [CurMonthSerialNumber] ,
                                                                     1,
                                                                            0

                                                         )

Regards,

M.R.

[Anonymous]

Hi @Anonymous ,

The CurMonthSerialNumber  is the same as the MonthSequentialNumber but based on the CurrentDate = TODAY().

Hope this clarifies

Cheers

CheenuSing