Forum Discussion

mostvp123's avatar
mostvp123
Kudo Collector
7 years ago
Solved

Custom Column to Separate Dates Help

Hi! I am currently working in the query editor with some 'date from' 'date to' data which records activities that people have to do in the future in hours. A sample for the data is as follows: ...
Need Help
  • AnthonyTilley's avatar
    AnthonyTilley
    7 years ago

    you could create a calculated colunm to divide the hours by a count of the activity id 

    Column =
    Var hours = Table1[Hours]
    var a = Table1[Activity]
     Var ca = CALCULATE(count(Table1[Activity]),FILTER(Table1,Table1[Activity] = a))
    Var ret = DIVIDE(hours,ca)
    Return ret

     
    Alternativly if you would prefer to have this already cacluated in teh query then add a second custom colunm before your split down step to divid the hours by the total days (you need to add 1 day to your date to as your times are all midnight so this would mean teh number fo days between 01/01/2019 and 02/01/2019 would be 1 day not 2.
    Formula below
    = [Hours] / Duration.Days(Date.AddDays([Date to],1)-[Date From])
     
    insert this add colunm between your added sutom and expanded date steps
AnthonyTilley's avatar
AnthonyTilley
Solution Sage
7 years ago

you could create a calculated colunm to divide the hours by a count of the activity id 

Column =
Var hours = Table1[Hours]
var a = Table1[Activity]
 Var ca = CALCULATE(count(Table1[Activity]),FILTER(Table1,Table1[Activity] = a))
Var ret = DIVIDE(hours,ca)
Return ret

 
Alternativly if you would prefer to have this already cacluated in teh query then add a second custom colunm before your split down step to divid the hours by the total days (you need to add 1 day to your date to as your times are all midnight so this would mean teh number fo days between 01/01/2019 and 02/01/2019 would be 1 day not 2.
Formula below
= [Hours] / Duration.Days(Date.AddDays([Date to],1)-[Date From])
 
insert this add colunm between your added sutom and expanded date steps