Forum Discussion

martinsdaniele's avatar
martinsdaniele
Frequent Visitor
4 years ago
Solved

Count Dates to get Average

Hi!

 

I need a card to show me the average of itens opened/day/employee. Is there a way for me to count this days?

For exemple, in the data below, I have 4 days (without the repetition) and 11 itens opened by 3 different employees.

 

The average would be:

 

Employee 1:  1,25 items opened by day

Employee 2:  0,75 items opened by day

Employee 3: 0,75 items opened by day

 

But I can't think of a way to count these days in which we have items...

 

EmployeeMaterialDate
1AXT00004/09/2022
2AXT00104/09/2022
3AXT00204/09/2022
1AXT00304/11/2022
2AXT00404/11/2022
1AXT00504/13/2022
1AXT00604/13/2022
1AXT00704/13/2022
3AXT00804/15/2022
2AXT00904/15/2022
3AXT01004/15/2022
Need Help
Show and Tell
  • SpartaBI's avatar
    SpartaBI
    4 years ago

    martinsdaniele 

    Avg Per Employee = 
AVERAGEX(
    VALUES('Table'[Employee]),
    VAR _min_date = CALCULATE(MIN('Table'[Date]))
    VAR _max_date = CALCULATE(MAX('Table'[Date]))
    VAR _days = INT(_max_date - _min_date)
    VAR _items = CALCULATE(COUNTROWS('Table'))
    RETURN
    DIVIDE(_items, _days)
)


    But I'm getting this:

    This are the days calculated for every employee:

    between 09/04 and 13/04 there 4 days not inclusive, but 5 inclusive so if 5 is what you want just add 1 to the VAR _days.
    Anyway, in either option I didn't ge tthe numbers you wrote. Even if i took distinct count of the days instead of their diff.
    If it should be your numbers, can you share the logic how you need to get to them
    For example, 
    how many days need to be for each employee (only days where he open a ticket or the diff days etc)

1 Reply

  • SpartaBI's avatar
    SpartaBI
    Icon for Community Champion rankCommunity Champion
    4 years ago

    martinsdaniele 

    Avg Per Employee = 
AVERAGEX(
    VALUES('Table'[Employee]),
    VAR _min_date = CALCULATE(MIN('Table'[Date]))
    VAR _max_date = CALCULATE(MAX('Table'[Date]))
    VAR _days = INT(_max_date - _min_date)
    VAR _items = CALCULATE(COUNTROWS('Table'))
    RETURN
    DIVIDE(_items, _days)
)


    But I'm getting this:

    This are the days calculated for every employee:

    between 09/04 and 13/04 there 4 days not inclusive, but 5 inclusive so if 5 is what you want just add 1 to the VAR _days.
    Anyway, in either option I didn't ge tthe numbers you wrote. Even if i took distinct count of the days instead of their diff.
    If it should be your numbers, can you share the logic how you need to get to them
    For example, 
    how many days need to be for each employee (only days where he open a ticket or the diff days etc)