Forum Discussion

anthonyr's avatar
anthonyr
New Member
1 year ago
Solved

Countrows for previous 12 months

Hi,

I've searched the forum and have found answers, but nothing is solving my problem. I am trying to count the number of incidents in a the previous 12 month period based on a month chosen on a slicer. It keeps just bringing back the current month. The dax i have is:

 

 

incident 12mths = 
var INC = 
    COUNTrows('SN Data') 
       
var calc = 
CALCULATE (
    INC,
    DATESINPERIOD('Date Table'[Date],
    MAX('Date Table'[Date]),
    -12,
    MONTH
    )
)

return IF (ISBLANK(calc), 0, calc)

 

 Any thoughts?

Need Help
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    Hi anthonyr ,
    Here’s an adjusted version of your DAX measure:

    incident 12mths = 
VAR SelectedDate = MAX('Date Table'[Date])
VAR StartDate = EOMONTH(SelectedDate, -12) + 1
VAR EndDate = EOMONTH(SelectedDate, 0)
RETURN
    CALCULATE(
        COUNTROWS('SN Data'),
        'Date Table'[Date] >= StartDate && 'Date Table'[Date] <= EndDate
    )

     
    This should give you the count of incidents for the previous 12 months based on the selected month in the slicer. Give it a try and let me know if it works!

     

    If this help you, please accept as solution and give a Kudo.

     

    thank you.

4 Replies

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Icon for Super User rankSuper User
    1 year ago

    Hi anthonyr ,
    Here’s an adjusted version of your DAX measure:

    incident 12mths = 
VAR SelectedDate = MAX('Date Table'[Date])
VAR StartDate = EOMONTH(SelectedDate, -12) + 1
VAR EndDate = EOMONTH(SelectedDate, 0)
RETURN
    CALCULATE(
        COUNTROWS('SN Data'),
        'Date Table'[Date] >= StartDate && 'Date Table'[Date] <= EndDate
    )

     
    This should give you the count of incidents for the previous 12 months based on the selected month in the slicer. Give it a try and let me know if it works!

     

    If this help you, please accept as solution and give a Kudo.

     

    thank you.

    • anthonyr's avatar
      anthonyr
      New Member
      1 year ago

      Super. that worked! thank you

  • danextian's avatar
    danextian
    Icon for Super User rankSuper User
    1 year ago

    Hi anthonyr ,

    Has your dates table been marked as a date table. Marking it as such automatically applies REMOVEFILTERS (DateTable) to the filter context every time you apply a filter on the DateTable[Date] column. If not you'll need to add REMOVEFILTERS manually

    incident 12mths =
VAR INC =
    COUNTROWS ( 'SN Data' )
VAR calc =
    CALCULATE (
        INC,
        DATESINPERIOD ( 'Date Table'[Date], MAX ( 'Date Table'[Date] ), -12, MONTH ),
        REMOVEFILTERS ( 'Date Table' )
    )
RETURN
    IF ( ISBLANK ( calc ), 0, calc )

     

     

    • anthonyr's avatar
      anthonyr
      New Member
      1 year ago

      It wasnt, but i have marked it as such now and still doesnt work. i tried your measure and still doesnt work. I have another measure counting something else (but doing a sum) and it works (see below) so not sure what to do 

      resolutions 12mths = 
var res = 
    SUM(Actions[Resolutions])
       
var calc = 
CALCULATE (
    res,
    DATESINPERIOD('Date Table'[Date],
    MAX('Date Table'[Date]),
    -12,
    MONTH
    )
)

RETURN IF (ISBLANK(calc), 0, calc)