Forum Discussion

Rigoleto's avatar
Rigoleto
Helper II
8 years ago
Solved

Stand alone Formulas

Hi, Since my skills about Power BI are in progress I have a lot of questions, currently I am writting a report that use a lot of business rules and there are some formulas(business rules) that need to be build, for instance: I have a datetime 20180615 10:13:00, do I need to do is to set up the date time to 20180615 07:00:00 and calculate the lapsed time between both datetimes, in this case the lapsed time will be 3:13 hours, well, I am wondering if I can create a Formula and add this formula during the calculation of a column, you can notice that this value will be like a constante, take the daily day and calculate the lapsed time.

 

 

Rigoleto

Tips and Tricks
  • v-juanli-msft's avatar
    v-juanli-msft
    8 years ago

    Hi Rigoleto

    Let me show you an example

    create a calculated column

    column =
CONCATENATE (
    CONCATENATE ( DATEDIFF ( [set up time], [datetime1], HOUR ), ":" ),
    MOD ( DATEDIFF ( [set up time], [datetime1], MINUTE ), 60 )
)

    Or, you can create a measure

    Measure =
CONCATENATE (
    CONCATENATE (
        DATEDIFF ( MAX ( [set up time] ), MAX ( [datetime1] ), HOUR ),
        ":"
    ),
    MOD ( DATEDIFF ( MAX ( [set up time] ), MAX ( [datetime1] ), MINUTE ), 60 )
)

     

    Best Regards

    Maggie

     

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    So, yes you could create a measure that does this calculation and then use this measure in other measures or column calculations.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    8 years ago

    Hi Rigoleto

    Let me show you an example

    create a calculated column

    column =
CONCATENATE (
    CONCATENATE ( DATEDIFF ( [set up time], [datetime1], HOUR ), ":" ),
    MOD ( DATEDIFF ( [set up time], [datetime1], MINUTE ), 60 )
)

    Or, you can create a measure

    Measure =
CONCATENATE (
    CONCATENATE (
        DATEDIFF ( MAX ( [set up time] ), MAX ( [datetime1] ), HOUR ),
        ":"
    ),
    MOD ( DATEDIFF ( MAX ( [set up time] ), MAX ( [datetime1] ), MINUTE ), 60 )
)

     

    Best Regards

    Maggie