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 CodeItem DescriptionSales MonthSales Quantity

123-ABC

SampDecember, 20205
123-ABCSampMarch, 20206
123-ABCSampNovember, 20202
144-AAASample TNovember, 20203
333-RRRSample TeOctober, 20204

 

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 CodeItem DescriptionJanuary 2020February 2020March 2020April 2020May 2020June 2020July 2020August 2020September 2020October 2020November 2020December 2020Summarized Sales
123-ABCSamp00600000002513

 

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

Customized ColumnReference
Item Code1
Item Description2
January 20203
.. 
... 
... 
December 202014
Summarized Sales15

 

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?

 

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!

7 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Community Support
    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!

  • Geradav's avatar
    Geradav
    Responsive Resident
    5 years ago

    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'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.