cancel
Showing results for
Did you mean:
Frequent Visitor

## Sum per year for date range

Hello,

Im stuck on getting the Sum of PricePerMonth for a Name in a year.

 Name StartDate EndDate PricePerMonth ABC 01/01/2019 30/06/2019 500 ABC 01/07/2019 30/06/2020 600 XYZ 01/01/2019 30/06/2020 500

For example

Name ABC
6 months * 500 = 3000 (2019)

6 months * 600 = 3600 (2019)

6 months * 600 = 3600 (2020)

Total 6600 for ABC in 2019

Total 3600 for ABC in 2020

Name XYZ

12 * 500 = 6000 (2019)

6 * 500 = 3000 (2020)

Preferred output in a new table visual:

 Name 2018 2019 2020 2021 ABC 0 6600 3600 0 XYZ 0 6000 3000 0

8 REPLIES 8 Super User IV

## Re: Sum per year for date range

@Sandertjuh what is logic on which year it should count towards. IN 2nd row for ABC, you have added it to 2019 but 3rd row, you are saying it is 2020.

Proud to be a Datanaut! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.  Super User III

## Re: Sum per year for date range

How about something like this measure on a visual with Table1[Name] on the rows:

```Measure =
SUMX (
Table1,
Table1[Price] * ( DATEDIFF ( Table1[StartDate], Table1[EndDate], MONTH ) + 1 )  //Check whether the +1 is necessary
)
```

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers Frequent Visitor

## Re: Sum per year for date range

@parry2k ,its like a contract so the start date / end date can be any month. I just need to figure out how to get the total amount in a year paid by customer ABC

Frequent Visitor

## Re: Sum per year for date range

@AlB, with this measure i get total of the contact but i dont see an option to get the values per year if the contract enddate is in the next year.

@AlB wrote:

How about something like this measure on a visual with Table1[Name] on the rows:

```Measure =
SUMX (
Table1,
Table1[Price] * ( DATEDIFF ( Table1[StartDate], Table1[EndDate], MONTH ) + 1 )  //Check whether the +1 is necessary
)```

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Super User III

## Re: Sum per year for date range

Create a one column-table witht the years Aux[Year] and place it in the visual too. Then something along these lines:

```Measure =
SUMX (
Table1,
Table1[Price]
* (
DATEDIFF (
VAR Start_ =
IF (
Aux[Year] < YEAR ( Table1[StartDate] ),
DATE ( YEAR ( Table1[StartDate] ), 1, 1 ),
Table1[StartDate]
)
VAR End_ =
IF (
Aux[Year] > YEAR ( Table1[EndDate] ),
DATE ( YEAR ( Table1[EndDate] ), 12, 31 ),
Table1[EndDate]
)
RETURN
Start_,
End_,
MONTH
) + 1
)
)
```

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers Frequent Visitor

## Re: Sum per year for date range

Created a one-column table Years with a Year column. When i try to setup the measure like you provide i get the message that i dont select a single value for the year, im selecting the column as in your example.

And there is something strange that the End_ variable is not found in the return section as 2nd param.

@AlB wrote:

Create a one column-table witht the years Aux[Year] and place it in the visual too. Then something along these lines:

```Measure =
SUMX (
Table1,
Table1[Price]
* (
DATEDIFF (
VAR Start_ =
IF (
Aux[Year] < YEAR ( Table1[StartDate] ),
DATE ( YEAR ( Table1[StartDate] ), 1, 1 ),
Table1[StartDate]
)
VAR End_ =
IF (
Aux[Year] > YEAR ( Table1[EndDate] ),
DATE ( YEAR ( Table1[EndDate] ), 12, 31 ),
Table1[EndDate]
)
RETURN
Start_,
End_,
MONTH
) + 1
)
)```

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers   Super User III

## Re: Sum per year for date range

```Measure =
SUMX (
Table1,
Table1[Price]
* (
DATEDIFF (
VAR Start_ =
IF (
SELECTEDVALUE(Aux[Year]) < YEAR ( Table1[StartDate] ),
DATE ( YEAR ( Table1[StartDate] ), 1, 1 ),
Table1[StartDate]
)
VAR End_ =
IF (
SELECTEDVALUE( Aux[Year] ) > YEAR ( Table1[EndDate] ),
DATE ( YEAR ( Table1[EndDate] ), 12, 31 ),
Table1[EndDate]
)
RETURN
Start_,
End_,
MONTH
) + 1
)
)```
Frequent Visitor

## Re: Sum per year for date range

The 2nd param is not working in this case, the second param can't find the var Start_ or End_. As first param in DATEDIFF i can see both the variable. @AlB wrote:
```Measure =
SUMX (
Table1,
Table1[Price]
* (
DATEDIFF (
VAR Start_ =
IF (
SELECTEDVALUE(Aux[Year]) < YEAR ( Table1[StartDate] ),
DATE ( YEAR ( Table1[StartDate] ), 1, 1 ),
Table1[StartDate]
)
VAR End_ =
IF (
SELECTEDVALUE( Aux[Year] ) > YEAR ( Table1[EndDate] ),
DATE ( YEAR ( Table1[EndDate] ), 12, 31 ),
Table1[EndDate]
)
RETURN
Start_,
End_,
MONTH
) + 1
)
)```

Announcements #### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section. #### ‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products. Top Solution Authors
Top Kudoed Authors
Users online (1,882)