cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
biel
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

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.

 

 

 

 

 

 


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

@ me in replies or I'll lose your thread!!!

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
Greg_Deckler
Super User IV
Super User IV


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


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

@ me in replies or I'll lose your thread!!!

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!




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,

 

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


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

@ me in replies or I'll lose your thread!!!

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!




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

 

 

 

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.

 

 

 

 

 

 


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

@ me in replies or I'll lose your thread!!!

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors