Dax query in power bi

Question

So i have a user name, start-date, end-date and the number of working hours.

User     Start-Date    End-DATE     Hour
Priyaj    2015-02-05    2015-02-08   32

I have to assign working hours value each day. How do I do that using dax?

Answer 1

Here is my table before I have made any changes

User        Start        End           Hour
UserA       01/01/2018   05/01/2018    32

Create a custom column to calculate the number of dates between [Start] and [End]

Dates = { Number.From([Start]..Number.From([End]) }

Create another custom column to calculate the number of [Hour]s divided by the number of list items. Do not expand the list before this step!

CountPerDay = [Hour] / List.Count([Dates])

Finally expand your list column to show the number of hours required per day. Note how the dates are in a numeric format. Changing the column data type to "Date" or "DateTime" will change these back to the correct value.

Using my example you should now have a table that looks something like the below:

User       Start         End          Hour     Dates         CountPerDay
UserA      01/01/2018    05/01/2018   32       01/01/2018    6.4
UserA      01/01/2018    05/01/2018   32       02/01/2018    6.4
UserA      01/01/2018    05/01/2018   32       03/01/2018    6.4
UserA      01/01/2018    05/01/2018   32       04/01/2018    6.4
UserA      01/01/2018    05/01/2018   32       05/01/2018    6.4     

If I add the UserB into the mix with the following records:

User        Start        End           Hour
UserB       01/02/2018   02/02/2018    10

The table updates as follows:

User       Start         End          Hour     Dates         CountPerDay
UserA      01/01/2018    05/01/2018   32       01/01/2018    6.4
UserA      01/01/2018    05/01/2018   32       02/01/2018    6.4
UserA      01/01/2018    05/01/2018   32       03/01/2018    6.4
UserA      01/01/2018    05/01/2018   32       04/01/2018    6.4
UserA      01/01/2018    05/01/2018   32       05/01/2018    6.4
UserB      01/02/2018    02/02/2018   10       01/02/2018    5
UserB      01/02/2018    02/02/2018   10       02/02/2018    5