Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Calculating the Average between two dates

Hi there,

I am going to create a suite of SLA type reports based on a "Incident" data source.

I thought I could create a measure to calculate the average between the 'created date' and the 'resolved date' and then filter for teams and a date range, but I can't seem to get the formula calculation to work.

I assume a measure for each of these is the right way to go about it as the table is loaded with all the dates / times I need.

What would be the best way to calculate this in a measure to use for a line graph?

Thanks all.

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

    First, you need to create a Date table and create a calculated column [IsWorkingDay] to recoginize the date is working day or not.

     

    IsWorkingDay = NOT WEEKDAY( 'Date'[Date] ) IN { 1,7 }

     

    Then create a calculated column as below in fact table to get the diff days between created date and resolved date. 

     

    Day Diff =
CALCULATE (
    COUNTROWS ( 'Date' ),
    DATESBETWEEN ( 'Date'[Date], 'Table'[created date], 'Table'[resolved date] - 1 ),
    'Date'[IsWorkingDay] = TRUE,
    ALL ( 'Table' )
)

     

    You can refer the content in the following links for the details.

    POWER BI DATEDIFF ONLY WORKING DAYS, HOURS AND SO ON…

    Counting working days in DAX

    If the above one is not what you want, please provide some sample data in your model(exclude sensitive data) and your expected result with sample or screenshot. Thank you.

    Best Regards

6 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      ah I think I know what is wrong, I need to limit the measure to only be for resolved or closed status also which I have done in a filter on the measure, but now I am stuck with it including weekends.

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi Anonymous ,

        First, you need to create a Date table and create a calculated column [IsWorkingDay] to recoginize the date is working day or not.

         

        IsWorkingDay = NOT WEEKDAY( 'Date'[Date] ) IN { 1,7 }

         

        Then create a calculated column as below in fact table to get the diff days between created date and resolved date. 

         

        Day Diff =
CALCULATE (
    COUNTROWS ( 'Date' ),
    DATESBETWEEN ( 'Date'[Date], 'Table'[created date], 'Table'[resolved date] - 1 ),
    'Date'[IsWorkingDay] = TRUE,
    ALL ( 'Table' )
)

         

        You can refer the content in the following links for the details.

        POWER BI DATEDIFF ONLY WORKING DAYS, HOURS AND SO ON…

        Counting working days in DAX

        If the above one is not what you want, please provide some sample data in your model(exclude sensitive data) and your expected result with sample or screenshot. Thank you.

        Best Regards

  • Syndicate_Admin's avatar
    Syndicate_Admin
    Administrator
    4 years ago

    Hello I want to get an average frequency between the last 4 dates of sale, I am a beginner and I want to know some method.

    Best regards