previous month, current month and next month on a shared axis

[Pernille1983]

Hi 

I have a problem with how to show my data in a chart where I want to have salesperson and previous, current and next month on a shared axis. At the same time I want to have a count of types of customer meeting as values.

I can't figure out is I should use measures or tables to get the right overview.

Below you can see my data and the chart as it looks so far. The bottom chart shows the seperation that I want, exept for I would like the periods to be dynamic, instead of specific months. Besides this I also have a date table with dates, months and so on.

TableChart so far

This is what I wish for

[RolandsP]

I solve this by following:

1) you need to add a new column in date table that gives a relative index to the month. Give 0, if it is a current month; give -1, if it is last month, and give 1, if it is next month. You can achieve that by so-called offsetting technic.

I have attached a code for Calendar table that you could use - see here. Just copy the code in blank query and you will have extensive Calendar table for your model. You should credit Avi Signh for this Calendar table code. You can find some more great stuff about Power BI on his website https://www.learnpowerbi.com/.

2) you will find column CurMonthOffset in your new Calendar table. Choose this column and insert in Visual level filters or Page level filters. And then choose only value -1, 0, 1 (these will return - last, current and next month).

[Pernille1983]

Hi

Thanks for the answer, this however still doesn't work, I only get the colum for current month, not seperate colums for previous and next month for each salesperson

[RolandsP]

HAve you linked new date table to your fact table?

[Pernille1983]

Yes and I use year and month slicer from the date table, so current month will be equal to the slicer values.

[Anonymous]

For this visual only, a slicer wont be necessary anymore. Do you have more visuals on the page?

[Pernille1983]

Yes I have and they need the slicer.

[Anonymous · Accepted solution]

Most simple methode will be if you edit the interaction of the visual containing previous, current and next month with other visuals on the report.

You can do this via menu item Format --> Edit Interaction button :

https://www.mssqltips.com/sqlservertip/4784/power-bi-visual-interactions/

[Pernille1983]

Ok, this works. now I can see all three periods.

Thanks

[Anonymous]

Do you have a separate table for dates? If not, please create one and add relationship between this table and your table with data.

Example to create Date-table:

Date = CALENDARAUTO()

Add another column in this Date-table:

Plus / min 1 month = IF(
IF('Date'[Date] > TODAY();0 - DATEDIFF(TODAY();'Date'[Date];MONTH);DATEDIFF('Date'[Date];TODAY();MONTH))
<= 1 &&
IF('Date'[Date] > TODAY();0 - DATEDIFF(TODAY();'Date'[Date];MONTH);DATEDIFF('Date'[Date];TODAY();MONTH))
>=-1;TRUE;FALSE) 

All dates will be evaluated if it is between first date of previous month and last date of next month. (value will be TRUE, else FALSE)

Add the Date and [Plus / min 1 month] columns to te visual and filter on TRUE

[Pernille1983]

Hi 

Thanks for the answer, sadly it doesn't work. 

Maybe it's because I forgot to write that I use a slicer pointing to actual year and month. So current month is equal to what is selected in the slicer.