Filter 4 table visuals by incremental months

[Jack_Scallan]

Hi,

I have 4 table visuals. Each table displays my quantity of stock ('Table A'[Quantity]), the demand from my customers ('Table B'[Demand]) and how much stock I should have leftover which is a measure [Quantity] - [Demand] = ('Table A'[Stock]).

The issue is that I want to filter table visual 1 by month and have the other tables filter by the following month. 

So if I filter table visual 1 by January, then table two should filter by February, table three should filter by March, and table four should filter by April.

Both Table A and Table B have a relationship to the Date table through 'Table A'[DueDate], 'Table B'[DueDate] and Date.

Please let me know if any additional information is required.

Many thanks,

Jack

[amitchandak]

@Jack_Scallan , Check if you can use small multiples with month year as small multiples on that visual

Another way is create this measure and use that as visual level filter

This Month =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Last Month =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = eomonth(_max1,-1)
var _min = eomonth(_max1,-2)+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

Same way you can get other months , use these measure in visual or visual level filter and check non blank value

[Jack_Scallan]

@amitchandak thanks for your help!

Can you help me define what [net] should be?

At it appears that small multiples can not be used for the table visual.

[v-rzhou-msft · Accepted solution]

Hi @Jack_Scallan ,

Do you want to show incremental months in four table visuals ? If you want to do this, I suggest you to create an unrelated Date table to help. If you create relationship between the Date table with other tables, your visual will only show the date in slicer due to the filter.

I suggest you to create measure filter to filter your visual in visual level filters.

Filter1 = IF(MAX('Table'[Date]) in VALUES('Calendar'[Date]),1,0)

Filter2 = IF(MAX('Table'[Date]) in CALENDAR(MAX('Calendar'[Date])+1,EOMONTH(MIN('Calendar'[Date]),1)),1,0)

Filter3 = IF(MAX('Table'[Date]) in CALENDAR(EOMONTH(MAX('Calendar'[Date]),+1)+1,EOMONTH(MAX('Calendar'[Date]),2)),1,0)

Filter4 = IF(MAX('Table'[Date]) in CALENDAR(EOMONTH(MAX('Calendar'[Date]),2)+1,EOMONTH(MIN('Calendar'[Date]),3)),1,0)

Add filter measure into visual level filter and set it to show items when value =1.

If this reply still couldn't help you solve your issue, please share a sample file with me.

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.