difference between quarters for Q1(Value1) - Q4(Value2)

[kamalbandam]

Hi,

I am trying to calculate a value where I am trying to place it in a matrix visual where In columns we have Quarter, In rows we have locations and In values I need  a measure of this sort :
If it is Q1 = Q1(Value1) - Q4(Value2)
          Q2 = Q2(Value1) - Q1(Value1)

          Q3 = Q3(Value1) - Q2(Value1)

          Q4 = Q4(Value1) - Q3(Value1)
For, Q2,Q3,Q4 it's simple current quarter - previous quarter formula. But I am not getting how to get the Q1.
I have tried these dax but it wasn't working.

IF(
VALUES('Date'[date].[QuarterNo]) = 1,
[Value1] - CALCULATE([Value2],FILTER('Date',date.QuarterNo =4)

[Value1] - CALCULATE(

            [Value1],

            FILTER('Date',DATEADD(

                'Date'[date].[Date],

                -1,

                QUARTER

            )

        )))

Anything of help would be great!

Thanks in Advance!
Kamal

[v-kelly-msft · Accepted solution]

Hi @kamalbandam ,

First create a calendar table as below:

dim table = ADDCOLUMNS(CALENDAR(MIN('Table'[Date]),MAX('Table'[Date])),"Quarter","Q"&QUARTER(''[Date]))

Then create a measure as below:

Measure = 
VAR currentquater=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),'Table'[Quarter]=MAX('Table'[Quarter])&&YEAR('Table'[Date])=YEAR(MAX('Table'[Date]))))
var _previous=CALCULATE(SUM('Table'[Value]),DATEADD('dim table'[Date],-1,QUARTER))
Return
currentquater-_previous

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!