Forum Discussion
Helper IIICumulative Bar Chart - How to Hide Months Without Affecting Values
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
Community ChampionPlot 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...)
Helper IIISean,
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
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.
Hi Anonymous ,
The CurMonthSerialNumber is the same as the MonthSequentialNumber but based on the CurrentDate = TODAY().
Hope this clarifies
Cheers
CheenuSing