Forum Discussion

joubertsaquett's avatar
joubertsaquett
Frequent Visitor
9 years ago
Solved

Graph with sum line

Good morning! I need a help, I'm developing a report where the graph showed me the total of admitted per month, I would like to know how to do it, add up the values ​​from the previous months...
  • dearwatson's avatar
    dearwatson
    9 years ago

    This is a classic problem that I have seen a few times, the solution is to create an "active employees" measure with a disconnected Calendar.

     

    To do this you will need a Calendar table with contigeous days in it.

     

    The fastest way to do this is create a new table 

    Calendar = CALENDARAUTO()... this will scan your data and build table with a list of dates. I recommend you build a proper date dimension but this does the trick.

     

    This Calendar[Date] is used to slice the data, no relationships are needed as we build a measure to lookup the value.

     

    Then your employee count measure:

    Employees = DISTINCTCOUNT(Table1[EmployeeID])

     

    Then you need a measure that counts the employees whose "admission date" is before (<=) the last date in the selected period and "termination date" is after the first date in the selected date period

     

    CALCULATE([Employees], FILTER(Table1, (Table1[Admission Date] <= LASTDATE (Calendar[Date])) && (Table1[DismissalDate] >= FIRSTDATE(Calendar[Date]))))