Nr. of open tickets by last day of previous month (Measure)

[IvanS]

Hi All,

I would need your help with capturing the nr. of open tickets by last day of previous month (in Measure). My data model is not storing the historical snapshot data, threfore I need to perform DAX calculation.

I have these columns:

• Ticket ID
• Status
• Creation Date
• Due Date
• Close Date

In order to capture the nr. of open tickets by last day of previous month, I would need to apply following logic:

1. Creation Date is equal or before the last day of previous month (to consider year change in December vs. January)

AND

2. Close Date is empty or within the actual month

I tried to use LASTDATE and PREVIOUSMONTH functions however these are table based and I would need to make this calculation dynamic to change over months.

Any suggestions?

Thank you
Ivan

[v-cgao-msft]

Hi @IvanS ,

I have created two tables.

The other is a calendar table. The relationship between them.

Please try this measure.

Nr. of open tickets by last day of previous month = 
VAR _current_year1 =
    YEAR ( TODAY () )
VAR _previous_month =
    MONTH ( TODAY () ) - 1
VAR _current_month =
    MONTH ( TODAY () ) 
VAR _value1 = CALCULATE (
        COUNT ( 'Table'[Ticket ID] ),
        FILTER (
            ALL ( 'Table' ),
            (MONTH ( 'Table'[Creation Date] ) = _previous_month ||  _previous_month =0)
                && MONTH ( 'Table'[Close Date] ) = _current_month
                && YEAR ( 'Table'[Creation Date] ) <= _current_year1
                || (MONTH ( 'Table'[Creation Date] ) = _previous_month
                    && YEAR ( 'Table'[Close Date] ) = BLANK ()
                    )
        )
    )
RETURN
    IF(_value1=BLANK(),0,_value1)

The result should be like this.

Attach the pbix file for reference. Hope it helps.

Best Regards,
Community Support Team_Gao

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[IvanS]

Hi @v-cgao-msft ,

is your code considering also the year change when considering December 2021 as previous month? In this case I think EOMONTH function is better solution.

MONTH(Table[Closed Time])=MONTH(EOMONTH(TODAY(), -1))

[v-cgao-msft]

Hi @IvanS ,

The code already takes this into account, we don't need to make any more changes to the expression.

It's February and we can simulate this special case with a slight change to the expression. For example.

Nr. of open tickets by last day of previous month = 
VAR _current_year1 =
    YEAR ( TODAY () )
VAR _previous_month =
    MONTH ( TODAY () ) - 2      // last month was in December 2021
VAR _current_month =
    MONTH ( TODAY () ) - 1      // current month is January 2022
VAR _value1 = CALCULATE (
        COUNT ( 'Table'[Ticket ID] ),
        FILTER (
            ALL ( 'Table' ),
            (MONTH ( 'Table'[Creation Date] ) = _previous_month || _previous_month =0)
                && MONTH ( 'Table'[Close Date] ) = _current_month
                && YEAR ( 'Table'[Creation Date] ) <= _current_year1
                || (MONTH ( 'Table'[Creation Date] ) = _previous_month
                    && YEAR ( 'Table'[Close Date] ) = BLANK ()
                    )
        )
    )
RETURN
    IF(_value1=BLANK(),0,_value1)

The result is 2, still correct.

Best Regards,
Community Support Team_Gao

[amitchandak · Accepted solution]

@IvanS , Using a date table, Remove crossfilter code if the table is not joined with date table

Current Employees = CALCULATE(COUNTx(FILTER(Ticket,Ticket[Start Date]<=eomonth(max('Date'[Date]),-1) && (ISBLANK(Ticket[End Date]) || Ticket[End Date]> eomonth(max('Date'[Date]),-1) )),(Ticket[TicketId ])),CROSSFILTER(Ticket[Start Date],'Date'[Date],None))

Refer this blog

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[IvanS]

Thank you @amitchandak for idea with EOMONTH.

Just one adjustment - as my date table is usually creating until end of the current year, I had to recreate using TODAY() function

MONTH(Table[Closed Time])=MONTH(EOMONTH(TODAY(), -1))

J