Forum Discussion

PhilJuniper's avatar
PhilJuniper
Frequent Visitor
2 years ago

Create chart from table with start and end

I have a resource table with the following fields: Project Resource Name Percentage allocation Start date End date From this, i want to create a chart and table that shows their allocation by...
Need Help
amitchandak's avatar
amitchandak
Super User
2 years ago

PhilJuniper , seem like similar to HR and between date problem 

 

Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM

 

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

 

PhilJuniper's avatar
PhilJuniper
Frequent Visitor
2 years ago

Thanks. 

From the links i found the info i needed.

Create a date table:

 

 

Date = 
ADDCOLUMNS (
    CALENDAR ( DATE ( 2023, 01, 01 ), DATE ( 2026, 12, 31 ) ),
    "Month no", MONTH ( [date] ),
    "Year", YEAR ( [date] ),
    "Month Year", FORMAT ( [date], "mmm-yyyy" ),
    "Qtr Year", FORMAT ( [date], "yyyy-\QQ" ),
    "Month year sort",
        YEAR ( [date] ) * 100
            + MONTH ( [date] ),
    "Qtr", QUARTER ( [date] ),
    "Month", FORMAT ( [Date], "mmmm" ),
    "Month sort", MONTH ( [Date] )
)

 

Then added a column to the date table that calculates the allocation by day:

 

Allocation = 
SUMX (
    FILTER (
        ResourceTable,
        ResourceTable[Start date] <= MAX ( 'Date'[Date] )
            && ResourceTable[End date] >= MAX ( 'Date'[Date] )
    ),
    [Allocation]
)