Variance % between Current and Previous Month's Open Tickets

[Caiz]

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!

[v-jayw-msft]

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

[amitchandak]

@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=6LUBbvcxtKA

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