Working with date periods

[biel]

Hi,

I'm facing an issue that I'm not able to solve, I hope someone could be able to solve it.

I have to datatables with the below columns:

Datatable 1: Employee Name, Department, Dept Assignment Initial Date, Dept Assignment Final Date.

Datatable 2: Employee Name, Salary Amount, Month

I'm interested to get an output showing the monthly cost of every department during the year but the issue that I have is that I'm not able to get the information of what employees have been assigned to a department during every month.

For example, I have that employee John has been working for IT from 01JAN18 to 30APR18. If I associate the Calendar Master data date to Assignment Initial date, John Salary overall Salary will be showed in January, instead of being showed in January, February, March and April.

Many thanks in advance!

[Greg_Deckler]

Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

[biel]

Hi Greg,

Many thanks, I took a look over the 2 measures but my case is slightly different. In your examples you have two tables, one with data and the other one with dates, in my case I have 3 tables one with periods data, another one with the amounts and the dates one.

How can I integrate the amount data from the third table? I tried to do the same that you do in the line 18

"Amount";[Amount]

but as it comes from a third table PowerBI is not able to find it.

Then, I tried to define this third table as follows, but it seems that I'm doing something wrong

VAR tmpAmounts = ADDCOLUMNS('Amounts';"Salary";Value(Sum([Salary]))

Thanks again,

[Greg_Deckler]

Going to need a bit of sample/example data to simulate your issue. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[biel]

Hi Greg,

As an input I have this datatable:

 And I would like to generate a new data table with the below look:

Many thanks in advance

[Greg_Deckler · Accepted solution]

OK, one way of doing that would be to start with this query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8ipNzFPSUXLz9HP0c3YFsgz1DfWNDAwtgExjfWMoO1YHrtIjCEiYIBSZYlPkHxAMJM0QqgyNUJQFpBakwszCaSFUEcQsnDZCVfk6Bnm7hnj6ueO2NxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Employee = _t, Dept = _t, #"Initial Date" = _t, #"Final Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Dept", type text}, {"Initial Date", type date}, {"Final Date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Dates( [Initial Date], Number.From( [Final Date]- [Initial Date]) +1, #duration(1,0,0,0))),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom")
in
    #"Expanded Custom"

Red text indicates a custom column added and expanded.

Then, in DAX create this calculated column:

Month = MONTH([Custom])

Then in DAX, you could create this table:

Table15 = 
SUMMARIZE('Table14',[Employee],[Dept],[Month])

Attached, you want Table14, Table15.