DAX - DistinctCount Previous Quarter Error/Help

[agd50]

 

Count of Incident (Previous Quarter) = 
VAR _Period = -1
VAR _Results =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Column] ),
    DATESINPERIOD ( 'Dim_Calendar'[Date], 
    TODAY(),
    _Period,
    QUARTER
    ) )
RETURN
IF ( ISBLANK ( _Results ),
0,_Results )

This dax formula almost works for the finding the value of the previous quarter - It just only counts how many values exists in a column (ie counting a 2 value as 1 value). Example below:

Not what I want above - I want to get the real number which is the 18. 

Thanks

[amitchandak · Accepted solution]

@agd50 , You can get last qtr based on today, assuming no selection

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

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([Net], FILTER('Date','Date'[Date] >=_min && 'Date'[Date] <= _max))

[agd50]

Very impressive, don't know how you do it. 

Works perfectly

[v-yanjiang-msft]

Hi @agd50 ,

I think the problem is that the function DATESINPERIOD didn't return the correct period. Please use the below dax to create a new table to check if it can return the expected period:

DATESINPERIOD ( 'Dim_Calendar'[Date], TODAY (), -1, QUARTER )

As the returned table of  DATESINPERIOD function can only contain dates stored in the dates column. In other words, if the 'Dim_Calendar'[Date] column don't contain all the dates of previous quarter, the returned period wil be cutted off.

Best regards,

Community Support Team_yanjiang

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

[agd50]

You are correct - it is counting from May - July instead of April - June