How to get the difference of current month for the current year data and previous year same month in ssrs?

0
votes

New Image

How to get the difference of current year current month total and previous year same month data in ssrs report while using mdx query.. I want to display the result in new column.

check this image :

enter image description here

1
reporting-services
If your MDX script is returning data from both years you can use a matrix component, check this. – alejandro zuleta
Hi Zuleta, yeah it is returning both years data, but i don't know how to subtract both years data.. I am already using matrix only, i didn't use MDX script i am directly fetching data from cube.. – chakri
please see the above image, in that i want the data for 2016 august sales order minus 2015 august sales order total.. – chakri
It can be done but you have to provide more details about your matrix. Maybe a screenshot of the matrix in the design window. – alejandro zuleta
Already i added the screen short(Check this image).. – chakri

1 Answers

0
votes

If your dataset returns data only from 2015 and 2016 years:

=SUM(
    IIF(
      MAX(Fields!Year.Value)=Fields!Year.Value,
      Fields!Products.Value,
      0
    )
 )
 -
 SUM(
    IIF(
      MIN(Fields!Year.Value)=Fields!Year.Value,
      Fields!Products.Value,
      0
    )
 )

If your dataset returns data from multiple more than two years you have to explicitely use the years you want to compare.

=SUM(
    IIF(
      LCase(Fields!Month_Year.Value)=MonthName(Today.Month) & "-2016",
      Fields!Sales_Order.Value,
      0
    )
 )
 -
 SUM(
    IIF(
      LCase(Fields!Month_Year.Value)=MonthName(Today.Month) & "-2015",
      Fields!Sales_Order.Value,
      0
    )
 )

UPDATE: Expression include Month validation to determine if the row corresponds to the current month, I assume your month field returns the month name i.e "August".

With the description you provided in your question and comments this could help you as start point. Good luck