Forum Discussion

DeanStearn's avatar
DeanStearn
New Member
2 years ago
Solved

Countrows for previousmonth

Hi, hopefully somebody can help me with this.   I am trying to get a count of all rows for the previous month and have tried different solutions. There are similar posts to this problem, but none o...
Need Help
  • Ahmedx's avatar
    Ahmedx
    2 years ago

    pls try this

     

     

    Measure=
VAR _Start =EOMONTH( TODAY(),-2)+1
VAR _End = EOMONTH( TODAY(),-1)

RETURN
CALCULATE(
    COUNTROWS(Data),
    USERELATIONSHIP(Data[Created], 'Calendar'[Date]),
    DATESBETWEEN('Calendar'[Date] ,_Start,_End
)

     

     

AllisonKennedy's avatar
AllisonKennedy
Community Champion
2 years ago

DeanStearn  - you're on the right track, just a few complexities of DAX that might be causing some grief.

 

1) Pay attention to the 'return value' of the function: I don't use the FIRSTDATE function, but if you refer to the documentation ( I use DAX.guide rather than the official Microsoft docs as dax.guide provides good examples and context too) : https://dax.guide/firstdate/ you'll see that the return for this function is 'Table' 

You're comparing this to 'Calendar'[Date], within the row context of a FILTER function, so trying to compare a scalar to a table, which can return some funny and unexpected results. 

 

It's better to use MIN and MAX functions instead (their return value is a SCALAR).

 

2) You could do this easily with my pattern for  Time Intelligence: 

https://excelwithallison.blogspot.com/2023/11/dax-time-intelligence-easy-pattern-to.html 

 

  • DeanStearn's avatar
    DeanStearn
    New Member
    2 years ago

    AllisonKennedy Thak you for this, however I am pretty new at this.

    If I understand correctly, I have to create a new base measure.

    I have one called 'Total Created Tickets' and it does this:

     

    COUNTA(Data[Key])
     
    Then I need to create another measure, and this is where I am coming unstuck.
     
    PreviousMonthOpenTickets =

    CALCULATE(
        [Total Created Tickets],
        'Calendar'[Date] >= DATEADD(MIN('Calendar'[Date]), -1, MONTH) &&
        'Calendar'[Date] < MAX('Calendar'[Date])
     
    Not sure if I am still on the right track or I have completely misunderstood you. Slightly baffled why I cannot use the PREVIOUSMONTH function now, or maybe I can ! Either way, another pointer would be greatly appreciated.
     
    Thanks
    Dean
    • AllisonKennedy's avatar
      AllisonKennedy
      Community Champion
      2 years ago

      DeanStearn , sorry for the delayed reply. Looks like you got this solved now, but for future readers the solutions would be:

       

      OPTION A: using DATEADD

      This option will respect the filters you put on your visuals / report for both the current month total and Previous Month Total, comparing week 1 of this month to week 1 of the previous month.

      PreviousMonthOpenTickets =
      CALCULATE(
          [Total Created Tickets],
          ' DATEADD('Calendar'[Date], -1, MONTH)
      )

       

      Option B: using Previous Month

      This option would expand the filters you put on your visuals / report to include the ENTIRE period of the previous month, comparing week 1 of this month to the ENTIRE previous month.

       

      PreviousMonthOpenTickets =

       

      CALCULATE(
          [Total Created Tickets],
          PreviousMonth('Calendar'[Date])
      )