Forum Discussion

sudhakar111's avatar
sudhakar111
Icon for Helper IV rankHelper IV
9 years ago
Solved

Difference % in Cross Tab

Hi Everyone,   I have a croos tab report in the below format. Years are the rows and Months are the columns.   Need to show Quantity and Difference %. Difference % = (Quantity (Current Year)-Quan...
Need Help
ThiagoLRamos's avatar
ThiagoLRamos
Regular Visitor
9 years ago
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.

sudhakar111's avatar
sudhakar111
Icon for Helper IV rankHelper IV
9 years ago

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.

  • sudhakar111's avatar
    sudhakar111
    Icon for Helper IV rankHelper IV
    9 years ago

    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.

     



    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        sudhakar111
        Icon for Helper IV rankHelper IV
        9 years ago

        Thanks a lot. It worked perfectly.