Countrows for previousmonth

[DeanStearn]

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

[Ahmedx · Accepted solution]

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]

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 

[AllisonKennedy]

@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.

[Ahmedx]

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]

Thank you @Ahmedx , much appreciated

[Ahmedx]

to understand how PREVIOUSMONTH works watch this video

https://www.sqlbi.com/tv/nextday-nextmonth-nextquarter-nextyear-previousday-previousmonth-previousye...

[Ashish_Mathur]

Hi,

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

[AllisonKennedy]

@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]

@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]

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

)

[parry2k]

@DeanStearn check this video, tweak the solution as you see fit. youtube.com/watch?v=UG1WxkBkM48