Forum Discussion

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)-Quantity(Previous Year)/Quantity (Previous

Year)). The Problem i am facing is there is one field quantity and it has to be split for curent year and previous year in the

difference % calculation.

Anonymous
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

10 Replies

TomMartens
Super User
9 years ago
Hey,

here you will find a pbix file

On the report page "YOY" there you will find a Matrix visual showing the growth over the years.

This is the DAX script that calculates the measure
YOY Growth = DIVIDE( (CALCULATE(SUM(FactWithDates[Amount])) -CALCULATE(SUM(FactWithDates[Amount]),DATEADD('Calendar'[Date],-1,YEAR))) ,(CALCULATE(SUM(FactWithDates[Amount]),DATEADD('Calendar'[Date],-1,YEAR))),BLANK())*100
Hope this helps
- 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)))
  )
  - TomMartens
    Super User
    9 years ago
    Hmm,
    
    this is odd, just downloaded the mentioned file and there is a report page
    
    and on this page there is the matrix visual I mentioned above, please re-download and check again.
    
    Your Formula looks not the same as mine, can you please provide the error Message and also easily reproducible sample data, without these information it is very hard or impossible to figure out what's going wrong on your side.
    
    Regards

Related content