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

Need Help

3 Replies

  • 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]
)
  • PhilJuniper's avatar
    PhilJuniper
    Frequent Visitor
    2 years ago

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