Calculate difference between 2 rounds column based

Question

Hello
I have ssas dataset which has
Quarter as parameter single choise
A data set which has
Q —Income
Q32020—200
Q42020—300
Q12021—500

My report should look like if choose Q42020

Q32020 — Q42020 — diff
200— 300— 100

Any one can give me tips of how to build it

Answer 1

I have ssas dataset which has

Is it a multi-dimensonal (MDX) or tabular (DAX) cube?
In MD you can add a calculated measure using ParallelPeriod (MDX) function to calculated the difference.

Answer 2

Hi @Radi Soufan
Maybe you can change your dataset definition like this:

CREATE TABLE TESTTABLE  
(Q VARCHAR(20),  
 INCOME INT )  
  
 insert into TESTTABLE  
 select 'Q32020' ,200  
 union all  
 select 'Q42020',300  
  union all  
 select 'Q12021',500  
  
  select * ,LAG(INCOME)OVER(ORDER BY (SELECT 1)) AS PREVIOUS_INCOME ,LAG(Q,1,'EMPTY') OVER(ORDER BY(SELECT 1)) AS PREVIOUS_Q from TESTTABLE   

And use expression like =Feilds!INCOME .value - Feilds!PREVIOUS_INCOME .value to get the diff between current row and previous row.
If you have any questions，please feel free to contact me.

Best Regards,
Isabella

---

If the answer is the right solution, please click "Accept Answer" and upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

Hi,

It could have been much more effective if you had provided the information to reconstruct the scenario instead of a vague description. For exact solution we will need the queries to create the table and insert the sample data as well as the exact expected result set according to the sample data.

In the meantime, according to your description, in first glance, the general solution looks like a simple case of PIVOT.

(1) To solve this in the server side try to execute PIVOT of the data which will return the set as Quarters as the column name and the Income in each Quarter will be the value of the column.

This should looks like

Q32020 — Q22020
200— 300

(2) Next all you need to do is to add the missing column of the diff by calculate the the different between the Quarters

If you don't succeed to follow the general explanation then please provide the missing information to reproduce the scenario so we will be able to provide the solution according to your specific sample data and schema

----------

Ronen Ariely
Personal Site | Blog | Facebook | Linkedin