Forum Discussion

christianfcbmx's avatar
christianfcbmx
Icon for Post Patron rankPost Patron
8 years ago
Solved

Need help with my formula

I have a power BI formula which I have in excel working correctly but in PBI is not getting right results: what this formula needs to do is to count working hours between 2 dates with 2 restrictions ...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    christianfcbmx

     

    Try this Calculated Column

     

    Calculated Column =
VAR MyStartDate =
    IF (
        HOUR ( TableName[Start] ) < 8,
        INT ( TableName[Start] )
            + 8 / 24,
        TableName[Start]
    )
VAR MyEndDate =
    IF (
        HOUR ( TableName[End] ) > 18,
        INT ( TableName[End] )
            + 18 / 24,
        TableName[End]
    )
VAR MyDates =
    ADDCOLUMNS (
        GENERATESERIES ( MyStartDate, MyEndDate ),
        "Day", WEEKDAY ( [Value], 2 )
    )
VAR StartTime =
    IF (
        HOUR ( MyStartDate ) <= 18,
        HOUR ( MyStartDate )
            + MINUTE ( MyStartDate ) / 60
            - 8
    )
VAR EndTime =
    IF (
        HOUR ( MyEndDate ) > 8,
        HOUR ( MyEndDate )
            + MINUTE ( MyEndDate ) / 60
            - 8,
        10
    )
VAR Result =
    10
        * (
            COUNTROWS ( FILTER ( mydates, [Day] <= 5 ) )
                - 1
        )
        - StartTime
        + EndTime
RETURN
    IF ( EndTime < StartTime, Result + 10, Result )
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

christianfcbmx

 

Try this Calculated Column

 

Calculated Column =
VAR MyStartDate =
    IF (
        HOUR ( TableName[Start] ) < 8,
        INT ( TableName[Start] )
            + 8 / 24,
        TableName[Start]
    )
VAR MyEndDate =
    IF (
        HOUR ( TableName[End] ) > 18,
        INT ( TableName[End] )
            + 18 / 24,
        TableName[End]
    )
VAR MyDates =
    ADDCOLUMNS (
        GENERATESERIES ( MyStartDate, MyEndDate ),
        "Day", WEEKDAY ( [Value], 2 )
    )
VAR StartTime =
    IF (
        HOUR ( MyStartDate ) <= 18,
        HOUR ( MyStartDate )
            + MINUTE ( MyStartDate ) / 60
            - 8
    )
VAR EndTime =
    IF (
        HOUR ( MyEndDate ) > 8,
        HOUR ( MyEndDate )
            + MINUTE ( MyEndDate ) / 60
            - 8,
        10
    )
VAR Result =
    10
        * (
            COUNTROWS ( FILTER ( mydates, [Day] <= 5 ) )
                - 1
        )
        - StartTime
        + EndTime
RETURN
    IF ( EndTime < StartTime, Result + 10, Result )