## Desktop

Member
Posts: 143
Registered: ‎04-06-2017

## Re: convert calclulated column datatype text to decimal number

Hi

Accurate formula:

Finding the hours between startdate and enddate columns in action table.

Hours Between = VAR Hrs = DATEDIFF(action[startdate],'action'[enddate],HOUR)                                                                                                        VAR Mins = MOD(DATEDIFF( action[startdate],'action'[enddate],MINUTE),60)

VAR TheTime =

IF (Hrs >0, COMBINEVALUES (" " , Hrs, "hrs", Mins, "mins"),

COMBINEVALUES(" " , Mins,"mins"))

Return TheTime

Once executed the calculated column, it display

Name           Hoursbetween

field              30 mins

public           1 hrs 20 mins

according to requirement , above Name "field","public" etc... display field total hours and public total hours etc...

for this i created new measure

measure = calculate(sum(action[Hoursbetween]) filter(site[name]="field"))

excecuted the query and drag it in to card visual it is showing

The function SUM cannot work with values of type string

thanks

Established Member
Posts: 180
Registered: ‎03-22-2018

## Re: convert calclulated column datatype text to decimal number

The measure

```Measure =
VAR MINstartdate =
SUM ( 'Action'[StartTime] )
VAR MAXStartdate =
SUM ( 'Action'[EndTime] )
VAR hrs =
DATEDIFF ( MINstartdate, MAXStartdate, HOUR )
VAR mins =
MOD ( DATEDIFF ( MINstartdate, MAXStartdate, MINUTE ), 60 )
VAR theTime =
IF (
hrs > 0
&& mins >= 1,
COMBINEVALUES ( " ", hrs, "hrs", mins, "mins" ),
IF (
hrs > 0,
COMBINEVALUES ( " ", hrs, "hrs" ),
COMBINEVALUES ( " ", mins, "mins" )
)
)
RETURN
theTime```

seems to be working as you desire, correct?

Member
Posts: 143
Registered: ‎04-06-2017

## Re: convert calclulated column datatype text to decimal number

Hi

The Measure you send, once i drag it into any visual like (card, table etc...) it is showing error

Community Support Team
Posts: 7,323
Registered: ‎05-02-2017

## Re: convert calclulated column datatype text to decimal number

Hi @kunuthuri,

Can you share you file? A dummy one is enough.

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member
Posts: 143
Registered: ‎04-06-2017

## Re: convert calclulated column datatype text to decimal number

Hi

before created above measure, action table startdate and enddate columns are in date\time datatype

once we created new measure , the measure in text data type

then we drag it throwing an error

thanks

Community Support Team
Posts: 7,323
Registered: ‎05-02-2017

## Re: convert calclulated column datatype text to decimal number

Surely, "3 hrs" is not a date. The text type won't be a problem.

Where did you drag it to?

What's wrong with my solution?

```Measure =
VAR total =
SUMX (
SUMMARIZE (
'table',
'table'[Name],
"seconds", DATEDIFF ( [startdate], [enddate], SECOND )
),
[seconds]
)
VAR hours =
INT ( total / 3600 )
VAR minutes =
INT ( MOD ( total, 3600 ) / 60 )
VAR seconds =
MOD ( MOD ( total, 3600 ), 60 )
RETURN
hours & " hours "
& minutes
& " minutes "
& seconds
& " seconds"```

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member
Posts: 143
Registered: ‎04-06-2017

## Re: convert calclulated column datatype text to decimal number

Hi

Above query, name column which column to conider?

Highlighted
Community Support Team
Posts: 7,323
Registered: ‎05-02-2017

## Re: convert calclulated column datatype text to decimal number

[ Edited ]

I got it from your posts. Can you share a sample? Or how can I know anything else about your data?

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member
Posts: 143
Registered: ‎04-06-2017

## Re: convert calclulated column datatype text to decimal number

Hi

sample data file.