Forum Discussion
Variance % between Current and Previous Month's Open Tickets
Hi there,
I'm struggling to create a measure that easily show the difference between the previous month's open tickets (1) and the current month's open tickets (8).
Previous Month Open Tickets =
CALCULATE (
COUNT ('October Tickets'[Status]),
FILTER (
ALL ('October Tickets'),
'October Tickets'[Status] = "Open"
),
FILTER (
ALL ('October Tickets'),
'October Tickets'[Created time].[Month] = [Current Month]
))
With this code I would like to do this, but it isn't working for this measure:
'October Tickets'[Created time].[Month] = [Current Month]-1
I also have a measure for the previous month which doesn't work in this measure either:
One Month Ago = [Current Month]-1
I get the error saying "does not support values of type TEXT with values of type NUMBER"
Any help would be appreciated!
2 Replies
Caiz , Try to use time intelligence with date table
First have a measure
open =
CALCULATE (
COUNT ('October Tickets'[Status]),
FILTER (
('October Tickets'),
'October Tickets'[Status] = "Open"
)example
MTD = CALCULATE([open ],DATESMTD('Date'[Date]))
last MTD = CALCULATE([open ],DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month = CALCULATE([open ],previousmonth('Date'[Date]))
next month = CALCULATE([open ],nextmonth('Date'[Date]))Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKATo get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
Hi Caiz ,
Please show some sample data and expected result.
The error message means the [current month] is a text type value.
Best Regards,
Jay
