Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
elshadlive
Frequent Visitor

How can I show the next 7 months in matrix column starting from a period in the slicer selection?

In the matrix columns, I need to show data for the next 7 months from the selected period in the slicer.
Period value is an integer in YYYYMM format

 

elshadlive_0-1676281892986.png

 

I have 4 tables:
Years [Year]
Months [Month, MonthName]
Periods [Period, Year, Month]
Sales [Period, Product, Amount]

elshadlive_1-1676281965554.png

 

Periods and Sales tables have a relationship via the Period field.
Years and Months tables are independent.

 

In matrix Columns settings I selected the "Show items with no data" option to show all months even if there are no sales.

Because the Periods table has no relationships with Years and Months tables, slicer selections do not affect Period columns in the matrix.

elshadlive_3-1676282109845.png

 

If I build a relationship between Periods and Years and Months tables, then a matrix shows only a period column for a selected Year-Month.

 

How can I show the next 7 months starting from the selected period and ending 7 months after the selected period?

1 REPLY 1
amitchandak
Super User
Super User

@elshadlive , when you need more then selected value in visual the slicer needs to be on an independent date table

 

example

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max1 = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max1, -1) +1

var _max = eomonth(_max1,7)
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

 

 

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.