active employee count per month

Hi Anonymous

Based on my test, i've added index column in query editor for date table. Below are measures for your reference.

Startcount = CALCULATE(COUNTROWS('Sample'))

Endcount = CALCULATE(COUNTROWS('Sample'),USERELATIONSHIP('Sample'[End Date],'Date'[Date]))

Leftcount = [Startcount]-[Endcount]

Final =
VAR a =
    SUMMARIZE (
        FILTER ( ALL ( 'Date' ), 'Date'[Index] <= MAX ( 'Date'[Index] ) ),
        'Date'[Index],
        'Date'[Month],
        "b", [Leftcount]
    )
RETURN
    SUMX ( a, [Leftcount] )

Regards,

Cherie

Hi Anonymous

please follow these steps:

After you have loaded your table ( named Data ) into Power BI, create a new table ( named DataModified) with the following DAX Query:

DataModified = 
SELECTCOLUMNS(
    GENERATE(
        Data,
        VAR StartDate = Data[Start Date]
        VAR EndDate = Data[End Date]
        RETURN
        CALENDAR( StartDate, EndDate - 1 )
    ),
    "Project", Data[project],
    "Name", Data[Name],
    "Date", [Date]
)

Afterwards create the calendar, projects and names dimensions:

Names = ALLNOBLANKROW( DataModified[Name] )

 

Projects = ALLNOBLANKROW( DataModified[Project] )

 

Calendar = 
ADDCOLUMNS(
    CALENDAR( DATE( 2017, 1, 1 ), DATE( 2019, 12, 31 ) ),
    "Month", MONTH( [Date] ),
    "Month Name", FORMAT( [Date], "MMMM" ),
    "Year", YEAR( [Date] ),
    "Month Year Name", FORMAT( [Date], "mmm yyyy" ),
    "Month Year Number", YEAR( [Date] ) * 100 + MONTH( [Date] )
)

Then create the relationships:

 

 

 

Then add a mtrix visual, drop the column 'Month year Name' from the Calendar table into the rows section and this measure into the values sections:

 

 

Active Employees = 
CALCULATE(
    COUNTROWS( DataModified ),
    LASTDATE( 'Calendar'[Date] )
)