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!

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!