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:


The data in this format is hard to use, so based on reccomendations from a different forum, I added a custom column which splits up the Date from, Date to columns into separate days as follows (see Date column):

This custom column is added by clicking on "Add Column" and then "Custom Column" in the query editor, with the code: ={Number.From([Date from])..Number.From([Date to])}
This works fine, however as can be seen above, the Hours column is simply repeated and is not divided by the number of new rows. Since the Dates have been split up, the hours must be divided. This is the desired result:
I can't seem to figure out a way to do this. Thank you for helping me out :)

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

1 Reply

  • 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