Forum Discussion

kamalbandam's avatar
kamalbandam
Icon for Helper III rankHelper III
5 years ago
Solved

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

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



Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    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!

1 Reply

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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!