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 of these resolve my issue.

 

AIM - Count up the number of rows in the previous month and display the result on a CARD visual

 

I have a calendar table listing all dates from 01/01/2020 - Table is called CALENDAR

I have a fact table containing my data - Table is called DATA

 

There are two relationships between these tables:

Calendar/Date to Data/Created     (this is the active relationship)

Calendar/Date to Data/Resolved    (this is the inactive relationship)

 

This is my measure:

Previous Month Open Tickets =

CALCULATE(
    COUNTROWS(Data),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Date] >= FIRSTDATE(PREVIOUSMONTH('Calendar'[Date])) &&
        'Calendar'[Date] < FIRSTDATE('Calendar'[Date])
    ),
        USERELATIONSHIP(Data[Created],'Calendar'[Date])
)
 
The card returns a result of (Blank)
 
If I use this code:
 
CALCULATE(
COUNTROWS(Data),
FILTER(
ALL('Calendar'),
'Calendar'[Date] >= FIRSTDATE(PREVIOUSMONTH('Calendar'[Date])) &&
'Calendar'[Date] < FIRSTDATE('Calendar'[Date]) + 1
),
USERELATIONSHIP(Data[Created], 'Calendar'[Date])
)
 
It returns a value of 3,989.
This is the total number of rows within the DATA table.
I am expecting a result of 1,370. This is the number of rows for the month of October 2023
 
If I use this code and specify the date range, I get the value I want of 1,370
 
CALCULATE(
    COUNTROWS(Data),
    USERELATIONSHIP(Data[Created], 'Calendar'[Date]),
    'Calendar'[Date] >= DATE(2023, 10, 1) && 'Calendar'[Date] < DATE(2023, 11, 1)
)

 

It is though Previousmonth does not work in the filter context. Too ensure no other filters are causing me a problem, I all using ALL('Calendar'), but still no luck.

 

Can somebody please tell me what I'm doing wrong.

 

Thanks

Dean

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
)

     

     

11 Replies

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

         

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Share some data (in a format that can be pasted in an MS Excel file), explain the question and show the expected result.

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