Forum Discussion

agd50's avatar
agd50
Helper V
2 years ago
Solved

Measure - Count All Data Except previous quarter?

Does anyone know how to change this measure (perhaps a new one) that can do a count excluding the previous quarter? (No YTD)

Count of Incident YTD -1Q =
VAR _today =
    TODAY ()
VAR StartDate =
    DATE ( YEAR ( _today ), 1, 1 )
VAR EndDate =
    DATE ( YEAR ( _today ), FLOOR ( MONTH ( _today ) - 1, 3 ) + 1, 1 ) - 1
VAR Results =
    CALCULATE (
        [Count of x],
        DATESBETWEEN ( 'Dim_Calendar'[Date], StartDate, EndDate )
    )
RETURN
    Results
Need Help
  • devesh_gupta's avatar
    devesh_gupta
    2 years ago

    agd50 Have you tried this one...

     

    over all  = Sum(Sales[Sales Amount])

     

    Last Qtr Today = 
    var _today = today()
    var _max = eomonth(_today, -1*if( mod(Month(_today),3) =0,3,mod(Month(_today),3)))
    var _min = eomonth(_max,-3)+1
    return 
    CALCULATE(Sum(Sales[Sales Amount]) , FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

     

    Then Total other than last qtr =

    [Over all] - [Last Qtr Today]

     

    In this, you can replace the measure sum with count as you need count.

    Also confirm are you using date filter on the page or not cause that can change the solution

3 Replies

  • devesh_gupta's avatar
    devesh_gupta
    Impactful Individual
    2 years ago

    Try this if this works for you.

     

    Solution 1:

    Count of Incident YTD Excluding Prev Quarter =
    VAR _today = TODAY()
    VAR StartDate = DATE(YEAR(_today), 1, 1)
    VAR EndDate = DATE(YEAR(_today), FLOOR(MONTH(_today) - 1, 3) + 1, 1) - 1
    VAR PrevQuarterStartDate = DATE(YEAR(_today), FLOOR(MONTH(_today) - 4, 3) + 1, 1)
    VAR PrevQuarterEndDate = DATE(YEAR(_today), FLOOR(MONTH(_today) - 1, 3) + 1, 1) - 1
    VAR Results =
    CALCULATE(
    [Count of x],
    DATESBETWEEN('Dim_Calendar'[Date], StartDate, EndDate),
    NOT DATESBETWEEN('Dim_Calendar'[Date], PrevQuarterStartDate, PrevQuarterEndDate)
    )
    RETURN Results

     

    Solution 2:

    Total Sales when there is not filter of slicer

     

    over all  = Sum(Sales[Sales Amount])

     

    Last Qtr Today = 
    var _today = today()
    var _max = eomonth(_today, -1*if( mod(Month(_today),3) =0,3,mod(Month(_today),3)))
    var _min = eomonth(_max,-3)+1
    return 
    CALCULATE(Sum(Sales[Sales Amount]) , FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

     

    Then Total other than last qtr =

    [Over all] - [Last Qtr Today]

    • agd50's avatar
      agd50
      Helper V
      2 years ago

      Thanks but I should have clarified I meant without YTD

  • devesh_gupta's avatar
    devesh_gupta
    Impactful Individual
    2 years ago

    agd50 Have you tried this one...

     

    over all  = Sum(Sales[Sales Amount])

     

    Last Qtr Today = 
    var _today = today()
    var _max = eomonth(_today, -1*if( mod(Month(_today),3) =0,3,mod(Month(_today),3)))
    var _min = eomonth(_max,-3)+1
    return 
    CALCULATE(Sum(Sales[Sales Amount]) , FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

     

    Then Total other than last qtr =

    [Over all] - [Last Qtr Today]

     

    In this, you can replace the measure sum with count as you need count.

    Also confirm are you using date filter on the page or not cause that can change the solution