cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IX
Super User IX

Re: Working with date periods

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.

 

 

 

 

 

 


---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

5 REPLIES 5
Super User IX
Super User IX

Re: Working with date periods


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


---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: Working with date periods

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,

 
Highlighted
Super User IX
Super User IX

Re: Working with date periods

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


---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: Working with date periods

Hi Greg,

As an input I have this datatable:iN.jpg

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

fin.jpgMany thanks in advance

 

 

 

Highlighted
Super User IX
Super User IX

Re: Working with date periods

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.

 

 

 

 

 

 


---------------------------------------

Not link spamming! If I posted a link in reply it is because I did assume you know how to search the web but thought it was the fastest way to fix your issue.

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors