Difference % in Cross Tab

[sudhakar111]

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.

[TomMartens]

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]

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]

---

@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]

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]

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.

[v-yuezhe-msft · Accepted solution]

@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-step... .

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]

Thanks a lot. It worked perfectly.

[TomMartens]

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-d...

[TomMartens]

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 

[sudhakar111]

I was able to see the matrix visual after downloading again. I uploaded some sample data. Kindly check it.

https://drive.google.com/file/d/0B409kD4MKi2RTjAyLXdCSldxdWs/view