DAX Measure to calculate number of lost days in different year from total number of days

0
votes

I am trying to calculate number of days for particular year based on calendar table that i have created.

For Example: I have 3 columns.
Event, number of days and Date when this event started

Event       DaysLost
Injury       30              25/12/2016  
Injury       588            06/08/2012

Days in 2016 - 6
Days in 2017 - 24

For the second case:

Days in 2012 - 146
Days in 2013 - 365
Days in 2014 - 77

Now for above case there are only 6 days which need to be counted in 2016 and the rest of the days should automatically be counted in 2017. But i cannot figure out how to do it.

In my output i would like to put years in one column and days lost for year in front of that particular year.

I have a calendar table and i want sum of days to populate for a particular year.

I tried calculating it by getting end date, by adding number of days to First start date and then if days were more that remaining days in that year. subtract remaining days from total days and remaining days should move to next year. But i cannot figure out how to keep adding days for next years if days extends for many years and list them after words.

Sept 4, 2017 Please see the excel solution below

Excel solution of the problem

2
excelpowerbipowerpivotdax
To be honest I'm confused. Can you make a mock of what you're trying to achieve, with input/desired output clearly defined? You said you tried something already - you probably should post that code and results you got (as well as expected results). – user5226582
I know you asked your question with regard to DAX, but I could provide you a solution using Power Query instead, if you like. – Marc Pincince
Yes pleas i'm open to any solution. i figured it out using excel tho. – jjgg
I've just added a screen shot of excel solution to my original post. please check it out and advise if it's possible with DAX or even a powerquery. – jjgg

2 Answers

0
votes

This is a Power Query based approach...

I started with this:

enter image description here

Then I added a custom column by clicking the Add Column tab and Custom Column button and completing the pop-up window like this:

enter image description here

...and clicking OK.

Then I changed the type for that new column by selecting it and then clicking the Transform tab and then Data Type and Date.

Then I added another custom column, completing the pop-up like this:

enter image description here

Then I added another custom column, completing the pop-up like this:

enter image description here

Then I added yet another custom column, completing the pop-up like this:

enter image description here

Then I expanded that last column I added by clicking on the enter image description hereat the top of the column and Expand to New Rows.

Then I added a final custom column, completing the pop-up like this:

enter image description here

Finally, I grouped by the Event, DaysLost, Started, and Year columns and summed the DaysLostForYear column by clicking the Transform tab and Group By button and completing the pop-up like this:

enter image description here

I end up with this:

enter image description here

You might want a different grouping, but this should get you close. It shows how many days were lost in the years associated with each instance of an injury's total days lost. For instance, the first injury, which was 30 days in duration, started on 12/25/2016: 7 of those days occurred in 2016 and 23 in 2017. The second injury was 588 days, started on 8/6/2012: 148 days were in 2012, 365 in 2013, and 75 in 2014.

Note that I count the started date as a lost day. Note also that I account for leap years.

I hope this helps.

Here's the query code:

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Event", type text}, {"DaysLost", Int64.Type}, {"Started", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Ended", each Date.AddDays([Started],[DaysLost]-1)),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Ended", type date}}),
#"Added Custom3" = Table.AddColumn(#"Changed Type1", "DaysYearStarted", each Number.From(Date.From(Text.From(Date.Year([Started]))&"/12/31")-[Started])+1),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "DaysYearEnded", each Number.From([Ended]-Date.From(Text.From(Date.Year([Ended])-1)&"/12/31"))),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "Year", each List.Numbers(Date.Year([Started]),Date.Year([Ended])-Date.Year([Started])+1)),
#"Expanded Custom" = Table.ExpandListColumn(#"Added Custom5", "Year"),
#"Added Custom1" = Table.AddColumn(#"Expanded Custom", "DaysLostForYear", each if [Year]=Date.Year([Started]) then [DaysYearStarted] else
 if [Year]=Date.Year([Ended]) then [DaysYearEnded] else
 if Date.IsLeapYear([Year]) then 366 else 365),
#"Grouped Rows" = Table.Group(#"Added Custom1", {"Event", "DaysLost", "Started", "Year"}, {{"DaysLostForYear", each List.Sum([DaysLostForYear]), type number}})
in
#"Grouped Rows"
0
votes

0) Importing the data from your Excel screenshot into Power BI results in this.

Data

1) Create a new column in that table using the following formula for end date (to help with future formulas).

EndDate = Injuries[First Start Date] + Injuries[Days]

End Date

You stated that you have a calendar table, so you can skip to step 3

2) Create a new table by clicking on Modeling -> New Table and entering the following formula. This gives a single column table with a list of years. 

Years = GENERATESERIES(2000, 2020, 1)

Year Table

3) Create another new table using the following formula. This gives a table with all of the fields from the initial data table crossjoined with the Year table that was just created. The formula also filters the resulting table to only return rows where the value in the Year column is between the First Start Date and the First Start Date plus Days. To learn more about the CROSSJOIN function, check of the documentation here.

InjuriesByYear = FILTER(
    CROSSJOIN(Years, Injuries),
    Years[Year] >= Injuries[First Start Date].[Year] &&
    Years[Year] <= Injuries[EndDate].[Year]
)

Crossjoin table

4) Create relationships from the InjuriesByYear table back to the initial data table and the Year table. This will help facilitate nicer reporting efforts.

Relationships

5) In the InjuriesByYear table, create a new column by clicking on Modeling -> New Column and entering the following formula. The first IF checks if all of the days lost are in a single year. The second IF handles when the days are spread across multiple years, with the True clause handling the first year, and the False clause handling all other years.

DayPerYear = IF(
    InjuriesByYear[Year] = InjuriesByYear[First Start Date].[Year] && InjuriesByYear[Year] = InjuriesByYear[EndDate].[Year], InjuriesByYear[Days],
    IF(
        InjuriesByYear[Year] = InjuriesByYear[First Start Date].[Year], DATEDIFF(InjuriesByYear[First Start Date], DATE(InjuriesByYear[First Start Date].[Year], 12, 31), DAY),
        DATEDIFF(DATE(InjuriesByYear[Year], 1, 1), MIN(InjuriesByYear[EndDate], DATE(InjuriesByYear[Year], 12, 31)), DAY) + 1
    )
)

DaysPerYear Column

6) To test it all out, create a pivot table as configured in below. Following these steps, the pivot table should match your Excel solution.

Pivot