Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Khalil87
Frequent Visitor

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

 

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 

 

Khalil87_0-1685370635697.png

 

 

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

to this shape 



Khalil87_1-1685370664383.png

 

 

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

Kindly share with me any paper related to this.

 

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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

View solution in original post

7 REPLIES 7
Khalil87
Frequent Visitor

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 

 

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

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 

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

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.

@Khalil87 


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

tamerj1
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors