Fill in full Calendar based on Start and End Date

[JPSingh]

I currently have a set of data in excel which gives me the start date and end date (in the future) for working hours of an employee. 

What I need to show is a complete breakdown of this on a calendar via a daily version so it should read something like the below view:

Are you able to advise what formula I should use. I have the working hours table (Image 1) and then I have a calendar built into BI from 2023 onwards

[divyed · Accepted solution]

Hello @JPSingh ,

You can make use of "Generate" to create such table.  Here is the DAX for the same

Table =

    GENERATE(

        Data,  //This is your table name containing data.

        CALENDAR(Data[StartDate], Data[EndDate])

    )

You can remove unwanted columns once done and rename columns as per need.

I hope this will solve your issue. Please mark this as a solution if this solves your problem. You can say special thank you by giving Kudos :).

[divyed]

You can also do this in SQL if your data is in database.

WITH CTE AS (
-- Recursive CTE to generate the dates
SELECT FName, StartDate AS DateValue, EndDate
FROM Test_Table
UNION ALL
SELECT FName, DATEADD(DAY, 1, DateValue), EndDate
FROM CTE
WHERE DateValue < EndDate
)
SELECT FName, DateValue AS [Date]

[TomasAndersson]

This is maybe not the most performant option, but should be enough for this:

Show if active = 
VAR __Start = MAX('Table'[Start])
VAR __End = MAX('Table'[End])
RETURN
IF(
    SELECTEDVALUE(Dates[Date]) >= __Start &&
    SELECTEDVALUE(Dates[Date]) <= __End,
    SELECTEDVALUE('Table'[Full Name])
)

        

Good luck!

[ryan_mayu]

@JPSingh 

you can try this in PQ

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCspPysxT8MjPT1HSUXL1dXUEUm4hrkDSXM8USBoZGJnom+obmUPYZvqGRvrGhkqxOtFKbjmJxRkK7vlFKYl56JotwMoNDPQN9Q3RdMYCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Full Name" = _t, Region = _t, FTE = _t, #"Daily Hour" = _t, Start = _t, End = _t]),
#"Added Custom" = Table.AddColumn(Source, "Custom", each {Number.From(Date.From([Start]))..Number.From(Date.From([End]))}),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
#"Changed Type" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}})
in
#"Changed Type"

pls see the attachment below

[JPSingh]

@ryan_mayu - Thank you for this. I think this is the solution I am looking for however, this doesn't seem to work. The data transforms the start and end to a date. When I change to text values to try and replicate what you have this doesn't work either. Do this need to be linked back to a Calendar?

[ryan_mayu]

if the column is already date type , you can try this

= Table.AddColumn(Source, "Custom", each {Number.From([Start])..Number.From([End])})

Change the Source to your last step name

[JPSingh]

@ryan_mayu  Apologise as am unsure what you meant by change the source name to your last step: I made the changes which are

The issue is when I apply the formla and go to expand the table your data is showing values however, my data when I get to the expand option shows me the below:

[ryan_mayu]

Your last step is changed type not Source,  then change Source to  #Changed Type in the coding.