Search code examples
variablesdaxtransitiondifference

first Quarter current Quarter difference

Iam fairly new to DAX and for that reason studying to catch more routine. Having problem trying to resolve. Am trying to count difference between first and the current Quarter.

start Q current Q diff = 
 //first quarter result for every year
 VAR firstQuarter =
    CALCULATE(
        [All Orderamount],
        dimDate[QuarterNo] = 1
    )

//all quarter sales
VAR allQuarters =
    SUMX(
        SUMMARIZE(
            dimDate,
            dimDate[YearQuarter],
            dimDate[Quarter],
            "Total Orders In Quarter", [All Orderamount]
        ),
        [Total Orders In Quarter]
    )

RETURN
    firstQuarter - allQuarters

"All Orderamount" measure just basic:

All Orderamount = 
    SUM(factOrders[Orderamount])

I had a plan to first get first quarter sales enter image description here

then count sales for all the quarters enter image description here

And after that just deduct one from another. The thing I cant figure out is how to have firstQuarter spread out on whole matrixs but I cant figure out how to do this enter image description here ...for this I also some extra measures for testing but cant figure out how to deal with context transition. Have tried removing filters from QuarterNo etc but at best it returns sales for all the data and not just for ongoing years first quarter.

or am I thinking this wrong? Should I have a different strategy to this? And could I have also attacher pbix-file to this? I used free sample data found from internet so could have also shared it

Solution

  • Hopefully you will have a Year column in your dimDate table, then it would be:

    Q1 =
  CALCULATE(
    [All Orderamount],
    REMOVEFILTERS(),
    dimDate[QuarterNo] = 1 && dimDate[Year] IN DISTINCT(dimDate[Year])
  )

    Alternative for readability only:

    Q1 =
  var yrs = DISTINCT(dimDate[Year])
  RETURN
    CALCULATE(
      [All Orderamount],
      REMOVEFILTERS(),
      dimDate[QuarterNo] = 1 && dimDate[Year] IN yrs
    )