Forum Discussion
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...
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 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
Community Champion
Community ChampionDeanStearn , 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])
)