Comparison between weeks

[andi2333]

Hi again,

continuing my learning journey for PowerBI, another - most likely - simple question:

I have a chart which shows the progress of open IT incidents over time  - calculated with the following query:

openTicketsOverTime =

VAR _cDate = MAX(MyCalendar[Date])

RETURN

CALCULATE(

    DISTINCTCOUNT('Aspen Tickets'[Number]),

    FILTER(

        'Aspen Tickets',

        'Aspen Tickets'[Created(opened_at)]  <= _cDate

        && ('Aspen Tickets'[Closed]) > _cDate || ISBLANK('Aspen Tickets'[Closed])

    )

)

This results in a chart like this:

I know would like to put another visual in my dashboard which compares the number of open tickets from this week to the week before and spits out the drop or increase in percentage...I am not sure what the best way to do this would be, and I would appreciate any suggestions.

Thanks

[BA_Pete]

Hi @andi2333 ,

I recognise that measure! 😉

I think this depends on which type of week you're using - are you using just regular calendar week (WEEKNUM) or are you using a custom fiscal week from a 4-4-5 calendar?

Pete

[andi2333]

Hi! Correct, and I really appreciate your support again!!

I am using a regular weeknum() function in my calendar. 
thanks!!

[BA_Pete]

No worries 🙂

You should be able to do something like this:

_openTickets_WoW_% =
VAR _currDate = MAX(MyCalendar[Date])
VAR _currOpen =
CALCULATE(
    DISTINCTCOUNT('Aspen Tickets'[Number]),
    FILTER(
        'Aspen Tickets',
        'Aspen Tickets'[Created(opened_at)]  <= _currDate
        && ('Aspen Tickets'[Closed]) > _currDate || ISBLANK('Aspen Tickets'[Closed])
    )
)
VAR _prevDate = _currDate - 7
VAR _prevOpen =
CALCULATE(
    DISTINCTCOUNT('Aspen Tickets'[Number]),
    FILTER(
        'Aspen Tickets',
        'Aspen Tickets'[Created(opened_at)]  <= _prevDate
        && ('Aspen Tickets'[Closed]) > _prevDate || ISBLANK('Aspen Tickets'[Closed])
    )
)
VAR _diff = _currOpen - _prevOpen
RETURN
DIVIDE(_diff, _prevOpen, 0)

Pete