Forum Discussion

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

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)))
)

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.

  • TomMartens's avatar
    TomMartens
    Super User
    9 years ago

    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

    https://docs.microsoft.com/en-us/sql/analysis-services/tabular-models/dax-formula-compatibility-in-directquery-mode-ssas-2016

  • sudhakar111's avatar
    sudhakar111
    Helper 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
      Helper 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