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
SAMEPERIODLASTYEAR may not work in our case as we are not comparing the exact dates.
For example for current year we should show month to date.But for previous year we need to show till end of the month.
This is the error i am seeing. I am using a single table.
Error Message:
MdxScript(Model) (6, 78) Calculation error in measure 'ZSales_1'[YOY Growth]: Function 'DATEADD' expects a contiguous selection when the date column is not unique, has gaps or it contains time portion.
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,
LydiaThanks a lot. It worked perfectly.