Counting Days in a Period and assigning to correct month

[phil91]

Hi,

I have a HR dataset showing individual entries for any absence periods. This table has a absence start date and absence end date columns. I want to calculate total days absent and filter by Month, Quarter, Year etc from my date table.

If I simply add a calculated column to the table as days absent = end date - start date I'm not sure how to handle the case where the absence period is across 2 months and the relationship needed with my date table to achieve the required filtering.

e.g Absence start date 27/08/2021 

      Absence end date 02/09/2021

      Days absent = 6

I need this to be counted as 4 days absent in August and 2 days absent in September when filtering by month/quarter from my date table

Any help would be appreciated.

Thanks

Phil

[v-eqin-msft]

Hi @phil91 ,

Glad to know you have found a solution, could you please Accept your last reply as the solution to make this thread closed? So more people could benefit from it.

Best Regards,
Eyelyn Qin

[Ashish_Mathur]

Hi,

The solution here will show you the process of creating one row per month - Generating Rows by Month for Date Ranges in Power Query.

Hope this helps.

[phil91 · Accepted solution]

Thank you everyone for their responses to this. I think I found a solution without having to create the extra columns in Power Query.

If I create 4 measures for each of the possible cases:

   1) Started in Period ended in Period

   2) Started in Period ended outside period

   3) Started before period ended in period

   4 )Started before period ended outside period

I can then pass 2 variables (mindate & max date) based on the date slicer into a table filter. If I adjust a sumx formula based on the 4 cases above I get to the relevant days needed to be counted

e.g 1) datediff(start date,end date)

      2) datediff(start date,max date)

Adding the 4 measures together gets me a total of relevant days in the selected date period

[v-eqin-msft]

Hi @phil91 ,

Please provide me with more details about your table and your expected output or share me with your pbix file after removing sensitive data.

How to Get Your Question Answered Quickly

How to provide sample data in the Power BI Forum

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[tex628]

If your dataset is of managable size you can possibly expand each row to include a row for each day of absence. This would mean that your example above would result in 6 rows:

27/8
28/8
29/8
30/8
1/9
2/9

This will dramatically increase the number of rows of your model so it requires that you have a limited amount of rows to start with. 

Br,
J

[phil91]

Thanks for your help, I'm not sure that will be mangeable as a solution as the absence fact table is essentially every absence in the last 4 years and will continue to grow in the future so the columns needed would just continue to grow.

[tex628]

Add a custom column in Power Query and copy this:

let
S = [Absence start date],
E = [Absence end date],
Y_S = Date.Year(S), 
Y_E = Date.Year(E), 
M_S = Date.Month(S),
M_E = Date.Month(E),
Datelist = List.Generate(
                    ()=>  #date(Y_S,M_S,1),
                    each _ <= #date(Y_E,M_E,1),
                    each Date.AddMonths(_, 1)
                )
in
Table.AddColumn(
    Table.TransformColumnTypes(
        Table.FromList(
            Datelist, Splitter.SplitByNothing(), {"Dates"}),
        {"Dates", type date}),
    "Number of Days",
each
if [Dates] = #date(Y_S,M_S,1) then Number.From(Date.EndOfMonth(S) - S)+1 
else
if [Dates] = #date(Y_E,M_E,1) then Number.From(E - Date.StartOfMonth(E))+1
else
Number.From(Date.EndOfMonth([Dates]) - Date.StartOfMonth([Dates])))

[tex628]

Well if thats the case i'd say you will need to expand on months instead. You would need to calculate the difference in months between start and end date, then expand that into new rows, applying the absencedays related to each month to each row. 

Give me a moment and ill write something up. 

/ J