Forum Discussion

Patriszjo's avatar
Patriszjo
Frequent Visitor
4 years ago
Solved

Average days running calculated for each date context

Dear All,

I've got a table with Service Desk Tickets which in simplification has four columns: Ticket ID, Date Created, Date Closed and Days Running which is the amount od days between Date Created and Date Closed or between Date Created and Today if Date Closed is null.

 

I need to visualize the Avg Days Running on a line chart with a timeline as X-Axis. The case is that if ticket was open from 1/1/2022 untill 3/30/2022 then its 88 days running value should be included in average calculations for January, February and March as well. 

Do you have any idea what dax measure or what type of relationship will help me in solving this issue?
I will be very grateful for any kind of help as I'm struggling with it for a while.

Best Regards,

Patriszjo

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    I am not sure whether I understood your question correctly, but please check the below picture and the attached file.

     

     

     

     

    Expected measure: =
VAR _newtable =
    ADDCOLUMNS (
        Data,
        "@Day running",
            IF (
                Data[Date closed] <> BLANK (),
                DATEDIFF ( Data[Date created], Data[Date closed], DAY ),
                DATEDIFF ( Data[Date created], TODAY (), DAY )
            )
    )
VAR _filternewtable =
    FILTER (
        _newtable,
        Data[Date closed] >= MIN ( 'Calendar'[Date] )
            && Data[Date created] <= MAX ( 'Calendar'[Date] )
    )
RETURN
    IF (
        HASONEVALUE ( 'Calendar'[Month name] ),
        AVERAGEX ( _filternewtable, [@Day running] )
    )

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I am not sure whether I understood your question correctly, but please check the below picture and the attached file.

     

     

     

     

    Expected measure: =
VAR _newtable =
    ADDCOLUMNS (
        Data,
        "@Day running",
            IF (
                Data[Date closed] <> BLANK (),
                DATEDIFF ( Data[Date created], Data[Date closed], DAY ),
                DATEDIFF ( Data[Date created], TODAY (), DAY )
            )
    )
VAR _filternewtable =
    FILTER (
        _newtable,
        Data[Date closed] >= MIN ( 'Calendar'[Date] )
            && Data[Date created] <= MAX ( 'Calendar'[Date] )
    )
RETURN
    IF (
        HASONEVALUE ( 'Calendar'[Month name] ),
        AVERAGEX ( _filternewtable, [@Day running] )
    )