Create Table - Week Completion Rows from date range

[HenryJS]

Hello everyone

I would like to create a new table (as shown in Table 2) from the data in Table 1.

The new table has one row for each candidate week end that is within the start and end date of the location.

Table 1 - 'Locations'

CandidateRef	CandidateName	PlacementStartDate	PlacementEndDate
62896	Jamie	01/04/2020	01/06/2020
19382	Henry	01/01/2020	01/02/2020
66279	Jorge	01/04/2020	01/05/2020

New table -

CandidateRef	CandidateName	Weekend
62896	Jamie	05/04/2020
62896	Jamie	12/04/2020
62896	Jamie	19/04/2020
62896	Jamie	26/04/2020
62896	Jamie	03/05/2020
62896	Jamie	10/05/2020
62896	Jamie	17/05/2020
62896	Jamie	24/05/2020
62896	Jamie	31/05/2020
19382	Henry	05/01/2020
19382	Henry	12/01/2020
19382	Henry	19/01/2020
19382	Henry	26/01/2020
66279	Jorge	01/04/2020
66279	Jorge	08/04/2020
66279	Jorge	15/04/2020
66279	Jorge	22/04/2020
66279	Jorge	29/04/2020

[HenryJS]

Hi all,

I would like to create a new table (as shown in table 2) from the data in table 1.

The new table has a row for each candidates week ending that lies withint the placement start date and end date.

Table 1 - 'Placements'

CandidateRef	CandidateName	PlacementStartDate	PlacementEndDate
62896	Jamie	01/04/2020	01/06/2020
19382	Henry	01/01/2020	01/02/2020
66279	George	01/04/2020	01/05/2020

New Table - 

CandidateRef	CandidateName	Week Ending
62896	Jamie	05/04/2020
62896	Jamie	12/04/2020
62896	Jamie	19/04/2020
62896	Jamie	26/04/2020
62896	Jamie	03/05/2020
62896	Jamie	10/05/2020
62896	Jamie	17/05/2020
62896	Jamie	24/05/2020
62896	Jamie	31/05/2020
19382	Henry	05/01/2020
19382	Henry	12/01/2020
19382	Henry	19/01/2020
19382	Henry	26/01/2020
66279	George	01/04/2020
66279	George	08/04/2020
66279	George	15/04/2020
66279	George	22/04/2020
66279	George	29/04/2020

[v-kelly-msft]

Hi @HenryJS ,

See the same thread I just replied below:

https://community.powerbi.com/t5/Desktop/Create-Table-Week-Completion-Rows-from-date-range/td-p/1062...

Pls note that the expected output about George has an error in Week ending dates,pls double check it.

Best Regards,
Kelly

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

[HenryJS]

is it possible to do this in DAX?

[FrankAT · Accepted solution]

Hello @HenryJS ,

you can do this with Power Query as follows:

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjOysDRT0lHySszNTAXSBob6Bib6RgZGBlCOGYQTqxOtZGhpbGEEFPZIzSuqhEobIqs1Qqg1MzMytwQKu6fmF6VjNdgUqjgWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [CandidateRef = _t, CandidateName = _t, PlacementStartDate = _t, PlacementEndDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"PlacementStartDate", type date}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Changed Type",{{"PlacementStartDate", Int64.Type}, {"PlacementEndDate", type date}}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Changed Type1",{{"PlacementEndDate", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type2", "Custom", each {[PlacementStartDate].. [PlacementEndDate]}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type3" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}}),
    #"Inserted Day Name" = Table.AddColumn(#"Changed Type3", "Day Name", each Date.DayOfWeekName([Custom]), type text),
    #"Filtered Rows" = Table.SelectRows(#"Inserted Day Name", each ([Day Name] = "Sonntag")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"PlacementStartDate", "PlacementEndDate", "Day Name"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Custom", "Week Ending"}})
in
    #"Renamed Columns"

Greetings FrankAT

[v-kelly-msft]

Hi @HenryJS ，

Create a new table using below dax expression:

Table 2 = CALENDAR(MIN('Table'[PlacementStartDate]),MAX('Table'[PlacementEndDate]))

Then create a column as below:

Column = IF(WEEKDAY('Table 2'[Date],2)=7,1,0)

Finally create a measure as below:

CandidateName = IF(SELECTEDVALUE('Table 2'[Date])>=MAX('Table'[PlacementStartDate])&&SELECTEDVALUE('Table 2'[Date])<=MAX('Table'[PlacementEndDate])&&MAX('Table 2'[Column])=1,MAX('Table'[CandidateName]),BLANK())

And you will see:

For the related .pbix file,pls click here.

Best Regards,
Kelly

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

[amitchandak]

Duplicate of : https://community.powerbi.com/t5/Desktop/Create-Table-Week-Ending-Rows-From-Date-Range/m-p/1061648

Refer to my blog on power bi: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Have week end date in your calendar to display

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 //Monday
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2) //Sunday

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],1)+1 //Sunday
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],1) //Saturday

For Calendar refer : https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...