Create chart from table with start and end

[PhilJuniper]

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 month (or week, or day). How can i convert the table above to something suitable for me to chart?

[PhilJuniper]

Hi, yes it is similar, but instead of counting rows i need to sum the "percentage allocation" field.

[amitchandak]

@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-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

[PhilJuniper]

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]
)