Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Visualize workload per project/customer/product.

Hi,   I have a data set with the following columns   - Forecasted Start date of project - Forecasted End date of project - Actual Start date of project (blank if not started) - Actual End date...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Hi Anonymous,

     

    Since you table store date in different columns as date range, I think time intelligence functions won't work for your scenario.

    In my opinion, I'd like to suggest create new tables to expand these date range.

     

    For example: Table formulas.

    Spoiler
    Forecast Detail Table = 
VAR _calendar =
    CALENDAR (
        MIN ( Table2[Forecasted Start date] ),
        MAX ( Table2[Forecasted End date] )
    )
RETURN
    SELECTCOLUMNS (
        FILTER (
            CROSSJOIN ( Table2, _calendar ),
            [Date] >= [Forecasted Start date]
                && [Date] <= [Forecasted End date]
        ),
        "Project", [Project],
        "Product",[Product],
        "Customer", [Customer],
        "Personnel category",[Personnel category],
        "Date", [Date]
    )


Actual Detail Table =
VAR _calendar =
    CALENDAR ( MIN ( Table2[Actual Start date] ), TODAY () )
RETURN
    SELECTCOLUMNS (
        FILTER (
            CROSSJOIN (
                FILTER ( ALL ( Table2 ), [Actual Start date] <> BLANK () ),
                _calendar
            ),
            [Date] >= [Actual Start date]
                && [Date] <= MAX ( [Actual End date], TODAY () )
        ),
        "Project", [Project],
        "Product", [Product],
        "Customer", [Customer],
        "Personnel category", [Personnel category],
        "Date", [Date]
    )

     

     

    Then you can direct use above tables to analysis and operate with each day of date range.

     

    Regards,

    Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Hi Anonymous,

 

Since you table store date in different columns as date range, I think time intelligence functions won't work for your scenario.

In my opinion, I'd like to suggest create new tables to expand these date range.

 

For example: Table formulas.

Spoiler
Forecast Detail Table = 
VAR _calendar =
    CALENDAR (
        MIN ( Table2[Forecasted Start date] ),
        MAX ( Table2[Forecasted End date] )
    )
RETURN
    SELECTCOLUMNS (
        FILTER (
            CROSSJOIN ( Table2, _calendar ),
            [Date] >= [Forecasted Start date]
                && [Date] <= [Forecasted End date]
        ),
        "Project", [Project],
        "Product",[Product],
        "Customer", [Customer],
        "Personnel category",[Personnel category],
        "Date", [Date]
    )


Actual Detail Table =
VAR _calendar =
    CALENDAR ( MIN ( Table2[Actual Start date] ), TODAY () )
RETURN
    SELECTCOLUMNS (
        FILTER (
            CROSSJOIN (
                FILTER ( ALL ( Table2 ), [Actual Start date] <> BLANK () ),
                _calendar
            ),
            [Date] >= [Actual Start date]
                && [Date] <= MAX ( [Actual End date], TODAY () )
        ),
        "Project", [Project],
        "Product", [Product],
        "Customer", [Customer],
        "Personnel category", [Personnel category],
        "Date", [Date]
    )

 

 

Then you can direct use above tables to analysis and operate with each day of date range.

 

Regards,

Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Anonymous thanks, this is exactly what i was looking for. Very helpfull!