Forum Discussion

Kostas's avatar
Kostas
Icon for Helper IV rankHelper IV
4 years ago
Solved

Average Days past in Line Graph

Hi Everyone, 

 

I am trying to show in a line graph the average days past since the reported date in any time of period but I cannot configure how to write my measure and need some help. 
I got a table with ID and a reported date for each ID and a status (open, closed), in my line graph I want to show in any period of time on average how many days past since the id is reported for those that are open (I am going to show it in month by month case). 

I have already created a calendar table as to use it in the x axis the date.

 

I hope that makes sense

 

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Kostas ,


    I am not sure if I understood your question correctly. Does this meet your desired output?

     

    AVG_DAYS = 
CALCULATE (
    AVERAGEX (
        VALUES ( 'Table'[ID] ),
        DATEDIFF (
            CALCULATE ( MAX ( 'Table'[reported date] ) ),
            MAX ( 'Calendar'[Date] ),
            DAY
        )
    ),
    FILTER (
        'Table',
        'Table'[reported date] < MAX ( 'Calendar'[Date] )
            && 'Table'[status] = "open"
    )
)

     

    My PBIX file:  Average Days past in Line Graph.pbix

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

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

    Kostas , if you need based on today

     

    day = datediff([Open Date], today() , Day)

     

    In case you need based on the selected date then you need to create a measure

     

    day = datediff(Min([Open Date]), selecteddate('Date'[Date])  , Day)

     

    Then you need to dynamic segmentation

    Dynamic segmentation -Measure to Dimension conversion: https://youtu.be/gzY40NWJpWQ

     

    • Kostas's avatar
      Kostas
      Icon for Helper IV rankHelper IV
      4 years ago

      Hello amitchandak , 

      I don't see to have available the function SelectedDate just yet at least. 
      Also I think I haven't expressed my issue correctly:
      The line graph in the x axis will have the dates from the calendar table, for each date section (i.e. January 2021) I want to show the average number of days that the tickets were open until January (until today as you mentioned above). The line then will continue and will show the avg result for those tickets that were open until February 2021 etc. 
      By doing that, the users will be able to view on average how many days all the available tickets were open in each month and compare in a MoM or YoY analysis.
      I know it is a bit complicated and please let me know if that cannot happen or does not make sense.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Kostas ,


    I am not sure if I understood your question correctly. Does this meet your desired output?

     

    AVG_DAYS = 
CALCULATE (
    AVERAGEX (
        VALUES ( 'Table'[ID] ),
        DATEDIFF (
            CALCULATE ( MAX ( 'Table'[reported date] ) ),
            MAX ( 'Calendar'[Date] ),
            DAY
        )
    ),
    FILTER (
        'Table',
        'Table'[reported date] < MAX ( 'Calendar'[Date] )
            && 'Table'[status] = "open"
    )
)

     

    My PBIX file:  Average Days past in Line Graph.pbix

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Kostas's avatar
      Kostas
      Icon for Helper IV rankHelper IV
      4 years ago

      Amazing Anonymous thanks for the help it seems that it works perfecly