Forum Discussion

PBIUWO's avatar
PBIUWO
Helper III
5 years ago
Solved

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

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 ...
Need Help
Tutorial Requests
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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!

PBIUWO's avatar
PBIUWO
Helper III
5 years ago

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's avatar
Frank
Frequent Visitor
5 years ago

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.