Forum Discussion

Chika100's avatar
Chika100
Frequent Visitor
1 year ago
Solved

Create columns - Number on roll using start and end dates

Create custom columns January to December 

  • I think I understand what you want to do.  A few steps to this idea:

     

    1. Relate the Staff table to a Calendar or Date table on the StartDate. 

    2. Create a measure counting the the entries in your staff column that are active in that month:

    ActiveEmployeeCount = 

    VAR MinDate = Min(DateTable[Date])
    VAR StartDate = SelectedValue(StaffTable[StartDate])
    CALCULATE(CountRows(StaffTable), ISBLANK(EndDate), StartDate >= MinDate)

     

    3.Create a matrix visual using your StaffID as rows and date table month as columns.  Place the new measure in the values.

     

1 Reply

  • I think I understand what you want to do.  A few steps to this idea:

     

    1. Relate the Staff table to a Calendar or Date table on the StartDate. 

    2. Create a measure counting the the entries in your staff column that are active in that month:

    ActiveEmployeeCount = 

    VAR MinDate = Min(DateTable[Date])
    VAR StartDate = SelectedValue(StaffTable[StartDate])
    CALCULATE(CountRows(StaffTable), ISBLANK(EndDate), StartDate >= MinDate)

     

    3.Create a matrix visual using your StaffID as rows and date table month as columns.  Place the new measure in the values.