Help! Converting decimal to date/time format string doesnt always work

[miguelsus2000]

Hi,

I've been pulling my hairs (what's new with Powerbi 🙂 ) trying to figure why some of my conversions from decimal to D H M format work for some decimals and not for others.  Notice how some minutes go beyond 60 and don't carry over the the hours and some hours go over 24 not carrying over to the days.

I feel I'm using very straightforward conversions:

To extract Days, Hours, and Mins I use the following formulate:

• AccumulatedTimeInStep-Days = rounddown([AccTimeInStep],0)
• AccumulatedTimeInStep-Hrs = rounddown((([AccTimeInStep] - rounddown([AccTimeInStep],0))*24),0)

• AccumulatedTimeInStep-Min = if(
  mod([AccTimeInStep]*1440-trunc([AccTimeInStep],0)*1440,60) < 60,
  mod([AccTimeInStep]*1440-trunc([AccTimeInStep],0)*1440,60),
  (mod([AccTimeInStep]*1440-trunc([AccTimeInStep],0)*1440,60)/60) - trunc(mod([AccTimeInStep]*1440-trunc([AccTimeInStep],0)*1440,60),0)
  )
  AccTimeInStep is the decimal displayed in the table in days.
  Has anyone found a more straightforward easier way?

Thank you.

M

[AllisonKennedy · Accepted solution]

@miguelsus2000  I would do this in Power Query if possible, looks like your Day is wrong, which is causing the problems with the other calculations.  I've followed the same logic as you though. I'm curious why that Day value is wrong though - can you provide a sample file or more context?

Paste this code into a new blank Query in Power Query editor and see if it makes sense/does the trick: 

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("LcxBCgAgCETRu7iOwTStziLe/xqBuXswn4mgCVbKESTgW2AYFwx+CgqRnrpRbG/o/vGE2z8S3EWZDw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [AccTimeInStep = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"AccTimeInStep", type number}}),
#"Inserted Days" = Table.AddColumn(#"Changed Type", "Days", each Number.RoundDown([AccTimeInStep]), Int64.Type),
#"Inserted Remaining Hours" = Table.AddColumn(#"Inserted Days", "Remaining Hours", each ([AccTimeInStep] - [Days])*24, type number),
#"Inserted Hours" = Table.AddColumn(#"Inserted Remaining Hours", "Hours", each Number.RoundDown([Remaining Hours]), Int64.Type),
#"Inserted Remaining Minutes" = Table.AddColumn(#"Inserted Hours", "Remaining Minutes", each ([Remaining Hours] - [Hours])*60, type number),
#"Inserted Minutes" = Table.AddColumn(#"Inserted Remaining Minutes", "Minutes", each Number.RoundDown([Remaining Minutes]), Int64.Type),
#"Inserted Remaining Seconds" = Table.AddColumn(#"Inserted Minutes", "Remaining Seconds", each ([Remaining Minutes] - [Minutes])*60, type number),
#"Inserted Seconds" = Table.AddColumn(#"Inserted Remaining Seconds", "Seconds", each Number.RoundDown([Remaining Seconds]), Int64.Type),
#"Inserted TimeKey" = Table.AddColumn(#"Inserted Seconds", "TimeKey", each ([Days] * 1000000) + ([Hours] * 10000) + ([Minutes] * 100) + [Seconds], type number),
#"Split Column by Position" = Table.SplitColumn(Table.TransformColumnTypes(#"Inserted TimeKey", {{"TimeKey", type text}}, "en-NZ"), "TimeKey", Splitter.SplitTextByPositions({0,2,4,6}, true), {"D", "HH", "MM", "SS"}),
#"Replaced Value" = Table.ReplaceValue(#"Split Column by Position","","0",Replacer.ReplaceValue,{"D"}),
#"Merged Columns" = Table.CombineColumns(#"Replaced Value",{"D", "HH", "MM", "SS"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"D:HH:MM:SS")
in
#"Merged Columns"

[Greg_Deckler]

@miguelsus2000 There are better ways to do this but it's late:

D = INT([Column1])
H = INT(([Column1] - INT([Column1])) / (1/24))
M = INT((([Column1] - INT([Column1])) - [H]/24) / (1/24/60))

[miguelsus2000]

Thank you Greg.  I might have tried something similar... it looks better sitll have a couple discrepancies.  I would have thought TRUNC is better as INT rounds to the closest integer; TRUNC strips the decimal.  See below.  This should be very straightforward, but wonder if there is anythign in my config not allowing the truncating of some of the values.

[AllisonKennedy]

@miguelsus2000 Are you creating measures or columns? From your formula it looks like [AccTimeInStep] is a MEASURE, which means the other values should only be MEASURES as well, or you'll get funny results. 

As I mentioned previously, it's more than just the values you highlighted that are incorrect: 

The problem stems from your INT/TRUNC/ROUNDDOWN step but it will be related to the CONTEXT of the [AccTimeInStep] measure.

@Greg_Deckler formulas should work like a charm in correct context, so we need to help you with context errors. 

@miguelsus2000 Can you please provide:

* screenshot of your fields in the matrix (ie what fields are in columns, rows and values)

* Paste the DAX for the [AccTimeInStep] measure directly into your post

* screenshot of your model view showing relationships between all the tables and tell us which tables the fields come from that are used in the matrix

Hopefully that makes sense?

Once we have that info we can troubleshoot the problem easily. 😀

[Greg_Deckler]

@miguelsus2000 I don't see the same discrepencies when using my formulas. Check out Table 11 in attached PBIX below signature. TRUNC and INT are only different when using negative numbers.

[miguelsus2000]

Thank you Greg... gonna try out and get back. 

[Greg_Deckler]

@miguelsus2000 Not sure, but this might make things clearer:

Conversion = 
    VAR __Value = [Column1]
    VAR __Days = INT(__Value)
    VAR __Hours = INT ( (__Value - __Days) * 24 )
    VAR __Minutes = INT ( (__Value - __Days - __Hours / 24) * 24*60 )
    VAR __Seconds = INT ( (__Value - __Days - __Hours / 24 - __Minutes/24/60) * 24*60*60 )
RETURN
    __Days&":"&__Hours&":"&__Minutes&":"&__Seconds