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.   Name StartDate EndDate PricePerMonth ABC 01/01/2019 30/06/2019 500 ABC 01/07/2019 30/06/2020 600 ...
Need Help
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

      • Sandertjuh's avatar
        Sandertjuh
        Frequent Visitor
        6 years ago

        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  Datanaut