Measure - Count All Data Except previous quarter?

[agd50]

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

[devesh_gupta · Accepted solution]

@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

[devesh_gupta]

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]

Thanks but I should have clarified I meant without YTD