Forum Discussion
Helper IIICumulative Bar Chart - How to Hide Months Without Affecting Values
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?
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
6 Replies
SeanCommunity Champion
Plot your CalendarTable[Date] on the X-Axis while using your Table [Date] in your Cumulative Total Measure! :smileyhappy:
(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?
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
v-huizhn-msftMicrosoft Employee
