Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I got a matrix displaying the name of clients in the rows and the sales per month on the column.
I got a Year slicer for this matrix. I have a measure that gets the December sales of the previous year.
What I intend to do is add another column to display the sales for the month divided by the said December sales for the previous year. My problem is that the December sales is cannot be used monthly columns.
I hope my question makes sense. Thank you in advance.
Solved! Go to Solution.
Hi @giorajo
Try this code
Total Sales = CALCULATE(SUM(Sales[Sales]))
Sales Rel to Prev Dec =
VAR __PrevYear =
YEAR ( MAX ( DateTab[Date] ) ) - 1
VAR __PrevDec =
CALCULATE (
[Total Sales],
DATESBETWEEN (
DateTab[Date],
DATE ( __PrevYear, 12, 1 ),
DATE ( __PrevYear, 12, 31 )
)
)
RETURN
CALCULATE ( DIVIDE ( [Total Sales], __PrevDec ), 0 )
2019 (vs Dec 2018)
2020 (vs Dec 2019)
Hope this helps
David
Hi,
Share the link from where i can download your PBI file.
Hi @giorajo
you have to create a measure for each month and user it in the table chart
Jan_Dec = [Jan]/[Calc_December]
Feb_Dec = [Feb]/[Calc_December]
...
Jan_Dec = [Dec]/[Calc_December]
Hope it resolves your issue? Did I answer your question? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!! Linkedin Profile |
Hi @giorajo
Try this code
Total Sales = CALCULATE(SUM(Sales[Sales]))
Sales Rel to Prev Dec =
VAR __PrevYear =
YEAR ( MAX ( DateTab[Date] ) ) - 1
VAR __PrevDec =
CALCULATE (
[Total Sales],
DATESBETWEEN (
DateTab[Date],
DATE ( __PrevYear, 12, 1 ),
DATE ( __PrevYear, 12, 31 )
)
)
RETURN
CALCULATE ( DIVIDE ( [Total Sales], __PrevDec ), 0 )
2019 (vs Dec 2018)
2020 (vs Dec 2019)
Hope this helps
David
User | Count |
---|---|
122 | |
109 | |
96 | |
59 | |
57 |
User | Count |
---|---|
143 | |
119 | |
102 | |
71 | |
61 |