Working out time remaining (Modulo)

• Alasdair Thomson

  Hall of Fame

  Points: 3630

  More actions

  January 13, 2016 at 4:37 pm

  #314927

  So I have a simple cube, a measure of total time (in seconds), to support better usage in reports I wanted to add a total remaining minutes & seconds as separate calculations so that these can be turned into HH:MM:SS as total duration.

  For example:

  Seconds - 3139628

  Total Hours - 872 (working in cube)

  Remaining minutes - 07 (need to calc)

  Remaining seconds - 08 (need to calc)

  These calcs would then be used to create a value in a report of 872:07:08 for total time. I can get this working in excel using the MOD function in excel

• Jacob Wilkins

  One Orange Chip

  Points: 27976

  More actions

  January 13, 2016 at 5:08 pm

  #1851776

  I seem to recall that with SSAS you have to use the form A-(INT(A/B)*B) as a substitute for A%B, since SSAS lacks a modulo operator.

  That would work out to something like this:

  HH=seconds/3600

  MM=INT((seconds-(INT(seconds/3600)*3600))/60)

  SS=seconds-(INT(seconds/60)*60)

  Cheers!

  EDIT: Fixed a typo.

• Alasdair Thomson

  Hall of Fame

  Points: 3630

  More actions

  January 13, 2016 at 5:22 pm

  #1851779

  Thanks for the quick response! Worked perfectly... Thank you very much!