OPening balance of unsolved tickets at the beginning of each month

[GuillaumeB]

I'm trying to figure out how to display the number of tickets at the beginning of a month that were open prior and left open.

I have a table with opening dates, closing dates. For the latest month it's no problem because I just filter using ISBLANK its the ones before that are giving me a headache.

So basically I want the count at the beginning of the month of tickets that were opened previously and not closed at of midnight on the first. 

I created a couple of measures for start of current month and End of previous month but I feel like I'm going way left field here.

Here is what I currently have that isn't working. 

Opening Balance = CALCULATE(COUNTROWS(Tickets),
FILTER(Tickets, Tickets[OpenDate]<[Start of Current Month]),
Filter(Tickets, Tickets[CloseDate]>[EndofPreviousMonth]
|| ISBLANK(Tickets[OpenDate]))))

[v-shex-msft]

Hi @GuillaumeB,

Maybe you can take a look at following link about create new table with expand detail date range and link to original table.

I think it should be suitable for your scenario.

Spread revenue across period based on start and end date, slice and dase this using different dates

Regards,

Xiaoxin Sheng

[GuillaumeB]

@v-shex-msft

Basically when I try to write this dax function for example, it doesn't seem to compare Ticket Open Date to the first row of my table which is Month. So I get no result anywhere because as far as I can tell it's comparing Open Date to Date (which ends up being Open date)

Opening Balance2 = CALCULATE(COUNTROWS(Tickets), 
 FILTER(Tickets, Tickets[Opened At]<RELATED(Dim_time[First Day of Month])),
 Filter(Tickets, Tickets[Close Date]>RELATED(Dim_time[First Day of Month])
 || ISBLANK(Tickets[Close Date])))

Note that First Date of Month is a calculated column if that makes any difference)

 I should explain better.

So I have a list of all service tickets for a year (includes open date and close date). I need to be able to create a table with all 12 months.

The first column should be Opening Balance, this shows the current number of tickers that were opened in the past but are yet to be closed as of that month (for example on May 1st, I should be able to see the total count of tickets that were open up until April 30th, and either have a close date, which means they haven't been resolved yet, or their close date is later than May 1st).

Does that really require creating an entirely new table or can a measure handle this?

[v-shex-msft]

Hi @GuillaumeB,

Expand table seems not fully suitable for your requirement, maybe you can take a look at following link which has similar requirement:

dax - how to count open tickets

Regards,

Xiaoxin Sheng

[GuillaumeB]

I must be doing something wrong since the table I get as a result is giving me a bunch of dates that have nothing to do with the ticket number associated with it, it's not even the right year. 

Opening Balance Calendar = 
VAR _calendar =
    CALENDAR ( MIN ( Tickets[Open Date] ), MAX ( Tickets[Close Date] ) )
RETURN
    SELECTCOLUMNS (
        FILTER (
            CROSSJOIN ( Tickets, _calendar ),
            Tickets[Open Date] < [Date]
                && ISBLANK(Tickets[Close Date])
                || Tickets[Close Date] >=  [Date]
        ),
        "Ticket", [Ticket Number],
        "Date", [Date]
    )