Reply
Frequent Visitor
Posts: 14
Registered: ‎06-07-2017
Accepted Solution

How can I calculate the difference of scores between previous quarter and current quarter

DateCategoryScore
01-01-2016A3
01-01-2016B2
01-01-2016C4
04-04-2016A2.5
04-04-2016B2.5
04-04-2016C4
08-08-2016A2
08-08-2016B3
08-08-2016C2
12-12-2016A3
12-12-2016B3
12-12-2016C3
02-02-2017A3
02-02-2017B2
02-02-2017C4
06-06-2017A3.5
06-06-2017B2
06-06-2017C2.5
   

 

 

In this table contain quarterly scores of different categories. How can i calculate the difference between previous quarter and current quarter in a calculated column.


Accepted Solutions
Highlighted
Frequent Visitor
Posts: 14
Registered: ‎06-07-2017

Re: How can I calculate the difference of scores between previous quarter and current quarter

Hi @v-qiuyu-msft

 

I got the Dax query. Below query works for me.

 

final result.PNG

 

CurrentQuarterScore-PreviousQuarterScore = 
VAR currentQuarterScore =
    CALCULATE ( SELECTEDVALUE ( Table1[Score] ) )
VAR selectedCategory =
    SELECTEDVALUE ( Table1[Category] )
VAR previousQuarterScore =
    CALCULATE (
        SUM ( Table1[Score] ),
        ALLEXCEPT ( Table1, Table1[Category] ),
        PREVIOUSQUARTER ( Table1[Date] )
    )
VAR difference =
    CALCULATE ( currentQuarterScore - previousQuarterScore )
RETURN
    difference

View solution in original post


All Replies
Moderator
Posts: 8,804
Registered: ‎03-06-2016

Re: How can I calculate the difference of scores between previous quarter and current quarter

Hi @nandukrishnavs,

 

You can create a calculated column below: 

 

QuarterDiff(current-previous) = TOTALQTD(SUM('Table1'[Score]),'Table1'[Date],ALL(Table1))-CALCULATE(SUM(Table1[Score]),ALL(Table1),PREVIOUSQUARTER('Table1'[Date]))
 
q2.PNG
 
Best Regards,
Qiuyun Yu 
Attachment
Frequent Visitor
Posts: 14
Registered: ‎06-07-2017

Re: How can I calculate the difference of scores between previous quarter and current quarter

[ Edited ]

Hi  @v-qiuyu-msft

 

Thanks for the reply. 

It seems results are not correct.


expected val.pngExpected values are mentioned in red color

 

 

Highlighted
Frequent Visitor
Posts: 14
Registered: ‎06-07-2017

Re: How can I calculate the difference of scores between previous quarter and current quarter

Hi @v-qiuyu-msft

 

I got the Dax query. Below query works for me.

 

final result.PNG

 

CurrentQuarterScore-PreviousQuarterScore = 
VAR currentQuarterScore =
    CALCULATE ( SELECTEDVALUE ( Table1[Score] ) )
VAR selectedCategory =
    SELECTEDVALUE ( Table1[Category] )
VAR previousQuarterScore =
    CALCULATE (
        SUM ( Table1[Score] ),
        ALLEXCEPT ( Table1, Table1[Category] ),
        PREVIOUSQUARTER ( Table1[Date] )
    )
VAR difference =
    CALCULATE ( currentQuarterScore - previousQuarterScore )
RETURN
    difference