Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculating monthly average with start date & end date

I need to calculate the number of days taken to complete a request, plus what is the monthly average based on the complexity of the request.  Basically would like to read the month in the start date...
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    Anonymous - Create  a column:

     

    Month = FORMAT([End date],"mmmm")

    Create another column

    MonthSort = MONTH([End date])

     

    For Month, specify a sort by column of MonthSort.

     

    Now create a visual with Month, Complexity and COUNT of any column or create a COUNTROWS measure.

AllisonKennedy's avatar
AllisonKennedy
Icon for Community Champion rankCommunity Champion
6 years ago

Anonymous 

 

Please use Greg_Deckler  's solution if you want to count XY Low in August, but are you saying that you only want to count the row if the start date and end date are in the same month? 

 

If that's the case, please try this measure: 

 

Total Persons = COUNTAX(FILTER('Table', MONTH('Table'[Start Date]) = MONTH('Table'[End date])), 'Table'[Person])

 
See attached pbix file below signature for setup on how to get this by month. 
 
I do also recommend having a DimDate table for this. https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html