Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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!
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!
Hi,
A matrix visual will work just fine.
@PBIUWOcan you try this - https://drive.google.com/file/d/1VeeUWzJIOmqaAlb9p7fgn0DnOGFABQED/view?usp=sharing
I don't have access to this
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
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.
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.