Convert Activity ID with start end date and cost to cost per activity per day

[Khalil87]

I need to convert the following sample of Activity ID with Start Date and Finish Date and Cost / Activity

I can calculate and get Activity Duration and Cost Per Activity Per day 

Then I need to convert this shape to New table with Activity ID and Date in days and Cost / activity / date 

to this shape 

To visualize the data as Cost and date S-Curve Primavera P6 

Kindly share with me any paper related to this.

[Khalil87]

thank you @tamerj1 It worked perfectly 

just asking for one more tip, how to consider Off-days and holidays on the calender to get zero cost on off-days, and distributr the cost on the remaining days, 

Thanks in advance 

[tamerj1]

@Khalil87 
Please try

NewTable =
SELECTCOLUMNS (
    GENERATE (
        'Table',
        FILTER (
            CALENDAR ( 'Table'[Start Date], 'Table'[Finsh Date] ),
            NOT ( WEEKDAY ( [Date], 2 ) IN { 6, 7 } )
        )
    ),
    "Activity ID", 'Table'[Activity ID],
    "Date", [Date],
    "Cost/Day", 'Table'[Cost/Day]
)

[Khalil87]

The calnder sheet worked fine, but the total is not correct, 

I discovered that the cost/day calculated from the total duration of finish - start, without considering week ends, 

so how to calculate Date Diff, of only work days?

Thank you 

[tamerj1]

@Khalil87 
Sorry, I got engaged with other subjects. Please create a new calculated in column in the original table.

Cost/Day Correct =
DIVIDE (
    'Table'[Cost/Duration],
    COUNTROWS (
        FILTER (
            CALENDAR ( 'Table'[Start Date], 'Table'[Finsh Date] ),
            NOT ( WEEKDAY ( [Date], 2 ) IN { 6, 7 } )
        )
    )
)

Then the new table would be

NewTable =
SELECTCOLUMNS (
    GENERATE (
        'Table',
        FILTER (
            CALENDAR ( 'Table'[Start Date], 'Table'[Finsh Date] ),
            NOT ( WEEKDAY ( [Date], 2 ) IN { 6, 7 } )
        )
    ),
    "Activity ID", 'Table'[Activity ID],
    "Date", [Date],
    "Cost/Day", 'Table'[Cost/Day Correct]
)

[Khalil87]

Thank you for your time, 

I think it's better to recalculate the Duration in correct way first then I can get the correct cost/day

But the dates is based on calender with Friday Off, and Holidays, so could you help me how can I control the calender generation table with Friday-Off and Table of Holidays.

[tamerj1]

@Khalil87 

FILTER (
CALENDAR ( 'Table'[Start Date], 'Table'[Finsh Date] ),
WEEKDAY ( [Date], 2 ) <> 5
&& NOT ( [Date] IN VALUES ( 'Holidays'[Date] ) )
)

[tamerj1 · Accepted solution]

Hi @Khalil87 

please try

NewTable =
SELECTCOLUMNS (
GENERATE ( 'Table', CALENDAR ( 'Table'[Start Date], 'Table'[Finsh Date] ) ),
"Activity ID", 'Table'[Activity ID],
"Date", [Date],
"Cost/Day", 'Table'[Cost/Day]
)