Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Working with date periods

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!

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    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.

     

     

     

     

     

     

5 Replies