Forum Discussion

Sandertjuh's avatar
Sandertjuh
Frequent Visitor
6 years ago

Sum per year for date range

Hello,

 

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

 

NameStartDateEndDatePricePerMonth
ABC01/01/201930/06/2019500
ABC01/07/201930/06/2020600
XYZ01/01/201930/06/2020500

 

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:

Name2018201920202021
ABC0660036000
XYZ0600030000

 

Need Help

8 Replies

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    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's avatar
      Sandertjuh
      Frequent Visitor
      6 years ago

      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

  • AlB's avatar
    AlB
    Community Champion
    6 years ago

    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  Datanaut

    • Sandertjuh's avatar
      Sandertjuh
      Frequent Visitor
      6 years ago

      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  Datanaut

       

      • AlB's avatar
        AlB
        Community Champion
        6 years ago

        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  Datanaut