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
)

     

     

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

 

 

DeanStearn's avatar
DeanStearn
New Member
2 years ago

Thank you Ahmedx 

This code now returns the figure that I am expecting.

 

However, I would be interested to know if somebody could explain why a function exists called PREVIOUSMONTH (https://dax.guide/previousmonth/) that I would have thought could have been used to get to my answer, but instead we have to use code and manipulate it in such a way to go back and forth in time. Just curious 🙂

 

Thank you all for your help, lots to learn !

 

Dean 

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

    try this

     

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

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

     PREVIOUSMONTH(FILTER(ALL('Calendar'[Date]), 'Calendar'[Date] >= _Start && 'Calendar'[Date] <= _End)))

     

    • DeanStearn's avatar
      DeanStearn
      New Member
      2 years ago

      Thank you Ahmedx , much appreciated

  • AllisonKennedy's avatar
    AllisonKennedy
    Community Champion
    2 years ago

    DeanStearn  Time intelligence functions are a bit mind boggling at first - I think it took me a few years to fully grasp the ins and outs of them. Lots of testing and trial and error. You can use the Previous Month function, and it will give you the ENTIRE month, not just the dates you have selected, following my same pattern:

     

    PreviousMonthOpenTickets =

     

    CALCULATE(
        [Total Created Tickets],
        PreviousMonth('Calendar'[Date])
    )
    The filtering for the date range that you were trying to do with the < filters within the DAX code actually happens in the visual / report layer, rather than needing to be applied in the DAX itself.