Forum Discussion
Difference % in Cross Tab
sudhakar111,
Create a calendar table in your PBIX file following the instructions in this blog: https://kohera.be/blog/business-intelligence/how-to-create-a-date-table-in-power-bi-in-2-simple-steps/ .
Then create relationship using Date field and INV_BILLING_DATE field between the calendar table and Sales_1 table, and create YOY Growth using the following DAX.
YOY Growth =(CALCULATE(SUM(Sales_1[BILLED_QTY])-CALCULATE(SUM(Sales_1[BILLED_QTY]),DATEADD('Calendar'[Date],-1,YEAR)))
)
Regards,
Lydia
sudhakar111 wrote:Thanks a lot. I checked the PBIX file but could not find the matrix visual.
Tried the formula in my report.
The below part of the formula is giving an error.
YOY Growth =
(CALCULATE(SUM(Sales_1[BILLED_QTY])-CALCULATE(SUM(Sales_1[BILLED_QTY]),DATEADD('Sales_1'[INV_BILLING_DATE],-1,YEAR)))
)
Hi, i think you can use SAMEPERIODLASTYEAR instead of DATEADD.
Hey,
sure one could use SAMEPERIODLASTYEAR
the reason why I try to avoid TIMEINTELLIGENCE functions is the fact, that they are basically just shortcuts and that they are not optimized for the use in DIRECTQUERY mode.
This is also true for the function DATEADD(...) but personally I have not encountered performance degradation.
The optimized functions are listed here