Forum Discussion

agd50's avatar
agd50
Helper V
3 years ago
Solved

DAX - DistinctCount Previous Quarter Error/Help

  Count of Incident (Previous Quarter) = VAR _Period = -1 VAR _Results = CALCULATE (     DISTINCTCOUNT ( 'Table'[Column] ),     DATESINPERIOD ( 'Dim_Calendar'[Date],     TODAY(),     _...
Need Help
  • amitchandak's avatar
    amitchandak
    3 years ago

    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))

v-yanjiang-msft's avatar
v-yanjiang-msft
Community Support
3 years ago

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's avatar
agd50
Helper V
3 years ago

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