Total in period per line

[NH2501]

Hi everyone,

I've tried to search and apply some solutions from the forum, but none worked for me.

I got a table with fields:

1. Case No.

2. Time Spend

3. Creation Date

I would like to calculate % participation of each case in a total time spent in a year when case was created.
But I cannot obtain the last figure - total time in a year of creation.

Data example:
Case No.   Time Spend   Creation Date    Total Time In Creation Year      % Time In Creation Year
01              1d                 01/01/21                    3d                                                    33%
02              2d                 01/01/21                    3d                                                    66%
03              3d                 01/01/22                    4d                                                   75%
03              1d                 01/01/22                    4d                                                    25%

Of course totaling of the last column should give 100% in each year.

[NH2501]

Nailed it! Thank you for help.

[NH2501]

Hi,

Thank you for suggestion, but it doesn't do the job, as limits the sum to the particular date, not the period (year in this case).

[tamerj1]

@NH2501 
I see only 4 different dates! actually 4 different rows with absoluteley nothing in common.

[tamerj1 · Accepted solution]

@NH2501 
Ok. The example from the original post gave the imprettion that the Creation Date column is allways the start of year date. 
Now you may try two methods. 
Method 1:

Total Time In Creation Year =
CALCULATE (
    SUM ( TableName[Time Spend] ),
    ALLEXCEPT ( TableName, TableName[Creation Date].[Year] )
)

Method 2:

First Create a new column for the Year: 

Year = YEAR ( TableName[Creation Date] )

Then

Total Time In Creation Year =
CALCULATE (
    SUM ( TableName[Time Spend] ),
    ALLEXCEPT ( TableName, TableName[Year] )
)

[tamerj1]

Hi @NH2501 
I assume Time Spend of an integer data type, then

Total Time In Creation Year =
CALCULATE ( SUM ( TableName[Time Spend] ), ALLEXCEPT ( TableName[Creation Date] ) )