Create a table between two dates

[Anonymous]

Hello people,

I need help!

I have a spreadsheet in .xls with some data as below that shows allocated people in some tasks:

Name	Start date	End date	Hours per day
Resource 1	01-01-2021	01-04-2021	2
Resource 2	04-01-2021	04-03-2021	4

I need to show this data in a table like this:

Name	Date	Hours per day
Resource 1	01-01-2021	2
Resource 1	01-02-2021	2
Resource 1	01-03-2021	2
Resource 1	01-04-2021	2
Resource 2	04-01-2021	4
Resource 2	04-02-2021	4
Resource 2	04-03-2021	4

I've tried some ways but I was not able to solve this problem.

Please, someone, have any idea how to solve this problem?

Thank you!

[DataInsights · Accepted solution]

@Anonymous,

Create a disconnected date table (no relationships):

Create the measures below. The second measure is necessary to calculate totals.

Base measure:

Daily Hours Calc = 
VAR vDate =
    MAX ( DimDate[Date] )
VAR vResult =
    IF (
        vDate >= MAX ( FactTable[Start date] )
            && vDate <= MAX ( FactTable[End date] ),
        MAX ( FactTable[Hours per day] )
    )
RETURN
    vResult

Totals measure:

Daily Hours = 
VAR vTable =
    ADDCOLUMNS (
        CROSSJOIN ( VALUES ( FactTable[Name] ), VALUES ( DimDate[Date] ) ),
        "@Hours", [Daily Hours Calc]
    )
VAR vResult =
    SUMX ( vTable, [@Hours] )
RETURN
    vResult

Create matrix:

If you add the Name and Date fields first, you will get the error below. However, once you add the measure, the error will resolve. You can avoid this error by adding the measure first.