Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX on Quarters

 

Hi, Inquring on how i can get this following data given quarterly date filters. The catch is:

 

* need to get comparative number of months only when quarter is incomplete.

 

Actual Quarter vs. Last Quarter and Target

 

Actual is say, still as of Jan'22 only.

Target is fully given for entire year already.

 

So when we select Q1 2022

  • Actual should be = Jan'22 (no problem here)
  • Last Quarter should be = Oct'21 only, not summing Oct'21 to Dec'21 since only 1 month data is available for current quarter
  • Target should be = Jan'22, should not include Feb'22 to Mar'22 even data is available since only 1 month data is available for current quarter

Also if we can get lifetime to date based on the selected quarter:

 

Example: Start Date of data is Oct'2017 latest is Jan'2022

When I select:

Q3 2021 Lifetime should be Oct'2017 to Sep'2021 sum

Q4 2021 Lifetime should be Oct'2017 to Dec'2021 sum

Q1 2022 Lifetime should be Oct'2017 to Jan'2022 sum

 

See Illustration.

 

Thanks in advance!

  • Whitewater100's avatar
    Whitewater100
    4 years ago

    Hello:

    You can do a couple of steps:

    Create a field in Date Table to show if Qtr Complete:

    Completed Qtr =
    VAR ThisQtrID =
    LOOKUPVALUE(Dates[QuarternYear], Dates[Date], TODAY())
    RETURN Dates[QuarternYear] < ThisQtrID
     
    Then first measure:
    Prev Qtr = CALCULATE([Total Sales], PREVIOUSQUARTER(Dates[Date]))
     
    Then last measure:
    Previous Qtr =
    var PQ = [Prev Qtr]
    var QtrComplete = IF(SELECTEDVALUE(Dates[QuarterCompleted]),1,0)
    return
    IF(QtrComplete = 1,
    PQ,
    CALCULATE([Total Sales], DATESBETWEEN(Dates[Date], STARTOFQUARTER(Dates[Date]), TODAY())
    )
    )
  • Whitewater100's avatar
    Whitewater100
    4 years ago

    If you want thru EOM prev month vs TODAY on last measure suggested you can use:

     

    Previous Qtr =
    var PQ = [Prev Qtr]
    var QtrComplete = IF(SELECTEDVALUE(Dates[QuarterCompleted]),1,0)
    return
    IF(QtrComplete = 1,
    PQ,
    CALCULATE([Total Sales], DATESBETWEEN(Dates[Date], STARTOFQUARTER(Dates[Date]),LASTDATE(PREVIOUSMONTH(Dates[Date])
    )
    )))

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous, last three like cumulative

     

    example

    Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

     

     

    only when qtr is selected

    Cumm Sales till last qtr = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected('Date'),'Date'[date] <=eomonth(max('Date'[date]),-3) ))

  • Whitewater100's avatar
    Whitewater100
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hello:

    You can do a couple of steps:

    Create a field in Date Table to show if Qtr Complete:

    Completed Qtr =
    VAR ThisQtrID =
    LOOKUPVALUE(Dates[QuarternYear], Dates[Date], TODAY())
    RETURN Dates[QuarternYear] < ThisQtrID
     
    Then first measure:
    Prev Qtr = CALCULATE([Total Sales], PREVIOUSQUARTER(Dates[Date]))
     
    Then last measure:
    Previous Qtr =
    var PQ = [Prev Qtr]
    var QtrComplete = IF(SELECTEDVALUE(Dates[QuarterCompleted]),1,0)
    return
    IF(QtrComplete = 1,
    PQ,
    CALCULATE([Total Sales], DATESBETWEEN(Dates[Date], STARTOFQUARTER(Dates[Date]), TODAY())
    )
    )
    • Whitewater100's avatar
      Whitewater100
      Icon for Solution Sage rankSolution Sage
      4 years ago

      If you want thru EOM prev month vs TODAY on last measure suggested you can use:

       

      Previous Qtr =
      var PQ = [Prev Qtr]
      var QtrComplete = IF(SELECTEDVALUE(Dates[QuarterCompleted]),1,0)
      return
      IF(QtrComplete = 1,
      PQ,
      CALCULATE([Total Sales], DATESBETWEEN(Dates[Date], STARTOFQUARTER(Dates[Date]),LASTDATE(PREVIOUSMONTH(Dates[Date])
      )
      )))
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks. Will check this solution.