Sum per year for date range

[Sandertjuh]

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

[AlB]

Hi @Sandertjuh 

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  

[Sandertjuh]

@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:

Hi @Sandertjuh 

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  

---

[AlB]

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  

[Sandertjuh]

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  

---

[AlB]

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

[Sandertjuh]

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

---

[parry2k]

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

[Sandertjuh]

@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