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(),
    _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

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

4 Replies

  • 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 

  • amitchandak's avatar
    amitchandak
    Super User
    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))

    • agd50's avatar
      agd50
      Helper V
      3 years ago

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

      Works perfectly