How to create dynamic data table to create customized matrix & populating its value?

[PBIUWO]

I have a Sales Data Table that has columns Item Code, Item Description, Sale Month, and Sale Quantity.

Item Code	Item Description	Sales Month	Sales Quantity
123-ABC	Samp	December, 2020	5
123-ABC	Samp	March, 2020	6
123-ABC	Samp	November, 2020	2
144-AAA	Sample T	November, 2020	3
333-RRR	Sample Te	October, 2020	4

I wanted to create a table/matrix visual, that has Item Code, Sales Months (January, February, March etc) with the Sales Quantity as the value and Summarized Sales.

Item Code	Item Description	January 2020	February 2020	March 2020	April 2020	May 2020	June 2020	July 2020	August 2020	September 2020	October 2020	November 2020	December 2020	Summarized Sales
123-ABC	Samp	0	0	6	0	0	0	0	0	0	0	2	5	13

So I have created a customized data table to use as a reference. 

Customized Column	Reference
Item Code	1
Item Description	2
January 2020	3
..
...
...
December 2020	14
Summarized Sales	15

How do I make the value of the months dynamic? so it changes the value to 12 months referenced to todays date? 

In the value of the Matrix, How do I make the Matrix values reference the new dynamic months?

[v-kelly-msft · Accepted solution]

Hi @PBIUWO ,

First create a date table as below:

calendar table = 
var _year=IF(MONTH(TODAY())<12,YEAR(TODAY())-1,YEAR(TODAY()))
var _month=IF(MONTH(TODAY())<12,MONTH(TODAY())+1,1)
Return
CALENDAR(DATE(_year,_month,1),TODAY())

Then create a column in the calendar table:

Month year = FORMAT('calendar table'[Date],"MMMM")&" "&YEAR('calendar table'[Date])

And a measure as below:

Measure = 
var _tab=SUMMARIZE('Table','Table'[Item Code],'calendar table'[Month year],"sumofsales",SUM('Table'[Sales Quantity]))
return 
SUMX(_tab,[sumofsales])+0

 Finally,you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Ashish_Mathur]

Hi,

A matrix visual will work just fine.

[smpa01]

@PBIUWOcan you try this - https://drive.google.com/file/d/1VeeUWzJIOmqaAlb9p7fgn0DnOGFABQED/view?usp=sharing

[PBIUWO]

I don't have access to this 

[Geradav]

@PBIUWO 

Not so sure to understand what you are trying to achieve with your dynamic table, but in Power BI ther is a visual call Matrix that works like a Pivot Table in Excel and that would give you the possibility to achieve exactly what you want with "table/matrix" you mention.

David

[PBIUWO]

@Geradav 

Yes, I am currently using a Matrix visual. But for each "value" being added onto the visual, it will create 2 columns. That's the flaw with the matrix. 

So for example, if I add Item Description from the data table, it will create Item Description for each column beside the Sales Quantity in the Matrix. 

[Frank]

As I understand it, you want Rolling 12 Months sales data.  Below is a way to accomplish that.

https://community.powerbi.com/t5/Desktop/Trailing-12-or-Rolling-12-month-sum/m-p/164419

In instances where my data has periods and not actual dates, I will make a calendar table and sequence them, i.e Jan 2020 = 1, Feb 2020 = 2, .....Jan 2021 = 13 and so on. Filter on current date and it should do waht you are asking.