cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

New Member

## Total in period per line

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.

1 ACCEPTED SOLUTION
Super User

@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] )
)``````
5 REPLIES 5
New Member

Nailed it! Thank you for help.

New Member

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).

Super User

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

Super User

@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] )
)``````
Super User

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] ) )

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.