Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
I need your help in building a matrix in such a way that I need to show "Sales2019" and "Sales2020" in rows as values and their relative metrics as numbers and need to be done for many Measure such as "Units2019" and "Units2020" etc.
Please find the structure and the required output.
Structure of the Data:
Date | Product | Region | Sales | Units |
01/01/2019 | A | US | 154 | 10 |
02/01/2019 | B | US | 542 | 21 |
01/01/2020 | A | US | 548 | 52 |
02/01/2020 | B | US | 783 | 58 |
Required Output:
Month | Jan | Feb |
Sales2019 | 154 | 542 |
Sales2020 | 548 | 783 |
YoY | 394 | 241 |
Units2019 | 10 | 21 |
Units2020 | 52 | 58 |
YoY | 42 | 37 |
Thanks in advance
Hi @Anonymous
1. Built a date table and create a relationship from it to Table1
2. Place DateT[Month] in columns of a matrix visual.
3. Create measures and place them in rows of the matrix visual. For instance:
Sales2019 = CALCULATE(SUM(Table1[Sales]), DateT[Year] = 2019)
and so on for the other measures
You could also use DateT[Year] in the columns of the visual so that the years do not have to be hard-coded in the measures.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi @AlB ,
Thanks for your swift reply. Is there a way to make the measure act dynamically on th dste selection?
@Anonymous
Measure will dynamically change automatically, you can just create a year slicer. This measure will change based on your year selection.
Measure = SUM(Table1[Sales])
Paul Zheng _ Community Support Team
@Anonymous , dynamic you can make using time intelligence
example
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))
or
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |