Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Current Quarter vs Last Quarter

Dear Team,  I want to compare two Quarter like Current Qtr Vs Previous Qtr... My data contains column period : Q1,Q2,Q3,Q4 

i can see measure PREVIOUSQUARTER Function doesn't work due date column not available in my dataset.

 

Suggest DAX Formula CQ, PQ Measure so i can substract CQ - PQ

 

Dataset

RegionYearValuePeriod
US

2024

1000

Q1

Canada2024200Q1
US20241300Q2
Canada2024300Q2
Russia2023200Q1
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Anonymous ,

    Based on my testing, please try the following methods:

    1.Create the sample table.

    2.Create the new measure to calculate current quarter.

    CQ = 
CALCULATE(
    SUM('Table'[Value]),
    'Table'[Year] = MAX('Table'[Year]),
    'Table'[Period] = MAX('Table'[Period])
)

    3.Create the new measure to calculate previous quarter.

    PQ = 
CALCULATE(
    SUM('Table'[Value]),
    'Table'[Year] = IF(MAX('Table'[Period]) = "Q1", MAX('Table'[Year]) - 1, MAX('Table'[Year])),
    'Table'[Period] = SWITCH(
        MAX('Table'[Period]),
        "Q1", "Q4",
        "Q2", "Q1",
        "Q3", "Q2",
        "Q4", "Q3"
    )
)

    4.Drag the measures into the matrix visual.

    5.The result is shown below.

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Icon for Super User rankSuper User
    2 years ago

    Anonymous , To achieve this first create a measure for Current Quarter and Previouse quarter

     


    CQ_Value =
    CALCULATE(
    SUM('SalesData'[Value]),
    'SalesData'[Period] = "Q" & QUARTER(TODAY())
    )

     

    For previous

    PQ_Value =
    VAR CurrentQuarter = QUARTER(TODAY())
    VAR PreviousQuarter = IF(CurrentQuarter = 1, 4, CurrentQuarter - 1)
    VAR PreviousYear = IF(CurrentQuarter = 1, YEAR(TODAY()) - 1, YEAR(TODAY()))
    RETURN
    CALCULATE(
    SUM('SalesData'[Value]),
    'SalesData'[Period] = "Q" & PreviousQuarter,
    'SalesData'[Year] = PreviousYear
    )

     

    Difference between both

    CQ_PQ_Difference = [CQ_Value] - [PQ_Value]

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Doesn't works... The result appear in one Quarter which was repiting the same number in Current Quarter of Q2 as Q1,Q3,Q4 is blank output and see below

       

       

      DAX Formula

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Example that you're showing all have previousquarter 'Date' column in dataset as my dataset don't find date colum.. how we can go for it

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Anonymous ,

    Based on my testing, please try the following methods:

    1.Create the sample table.

    2.Create the new measure to calculate current quarter.

    CQ = 
CALCULATE(
    SUM('Table'[Value]),
    'Table'[Year] = MAX('Table'[Year]),
    'Table'[Period] = MAX('Table'[Period])
)

    3.Create the new measure to calculate previous quarter.

    PQ = 
CALCULATE(
    SUM('Table'[Value]),
    'Table'[Year] = IF(MAX('Table'[Period]) = "Q1", MAX('Table'[Year]) - 1, MAX('Table'[Year])),
    'Table'[Period] = SWITCH(
        MAX('Table'[Period]),
        "Q1", "Q4",
        "Q2", "Q1",
        "Q3", "Q2",
        "Q4", "Q3"
    )
)

    4.Drag the measures into the matrix visual.

    5.The result is shown below.

    Best Regards,

    Wisdom Wu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.