Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User


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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.