Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
DeanStearn
New Member

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

1 ACCEPTED SOLUTION
Ahmedx
Super User
Super User

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
)

 

 

View solution in original post

11 REPLIES 11
Ahmedx
Super User
Super User

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
)

 

 

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 

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

Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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

 

Screenshot_1.png

Thank you @Ahmedx , much appreciated

Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
AllisonKennedy
Super User
Super User

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

AllisonKennedy_0-1700427303718.png

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 

 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

@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

@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], -1MONTH)
)

 

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

 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.