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

[jaygill]

Hello everyone, 

I'm fairly new to this community and DAX. and i have run into a problem at work. A help would be much appreciated.

 i'm trying to calculate number of days for particular year based on calender 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 second case

Days in 2012 - 146

Days in 2013 - 365

Days in 2014 - 77

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

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

Thanks in Advance.

[jaygill]

I'm still looking for a DAX solution. However i figured it out using excel.

Please see a attached screen shot. 

If someone could provide a DAX solution it'd be much appreciated. 

[Anonymous · Accepted solution]

Hi @jaygill

Try the following

1. Remove any relationship between calendar table and the fact table of injury.

2. If you do not have a Year value in calendar table , create a column called Year = Year(Calendar[Date]) in the calendar table.

3. Create a measure ( Using your exccel formula )

LostDays =
VAR MAXYEAR = Max('Calendar'[Year])
VAR MINDATE= Min(Injury[StartDate])
VAR MAXDATE = MAX(Injury[EndDate])
VAR CALYEAR = MAX('Calendar'[Year])
RETURN
IF (CALYEAR >= YEAR(MINDATE) && CALYEAR <= YEAR(MAXDATE),
                  IF(YEAR(MAXDATE) > CALYEAR,               
                    DATEDIFF(IF (CALYEAR <> YEAR(MINDATE),
                              DATE(CALYEAR,1,1) ,MINDATE ) ,ENDOFYEAR('Calendar'[Date] ) ,DAY) ,
        DATEDIFF(IF (CALYEAR <> YEAR(MINDATE),
                              DATE(CALYEAR,1,1) ,MINDATE ) ,MAXDATE,DAY ) ) , blank )

4. Now plot a matrix chart with

   Number from fact table on Rows

   Year from Calendar Table on Columns

   LostDays measure as values.

Sample out of data on the left hand side

If this works for you please accept this as a solution and also give KUDOS.

Cheers

CheenuSing

[jaygill]

I think i should Total number of days to First start and get a end date but dont know how to get number of days for different year if given days exceeds the total remaining days in that year and futher on.

For Example:  if days gives are 400 and Start date in 1/1/2015. i could get the end date by adding 400 to 1/1/2015 and get number of days for that particular year by subtrating nymber of days left in that year but how to automatically count the rest 35 days in next year.