cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sandertjuh
Frequent Visitor

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

 

8 REPLIES 8
Super User IV
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.






Did I answer your question? Mark my post as a solution.

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





Super User III
Super User III

Re: Sum per year for date range

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

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

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


 

Super User III
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  Datanaut

Sandertjuh
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  Datanaut


 

2019-10-02_07h57_28.png

Super User III
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
        )
)
Sandertjuh
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.

 

2019-10-02_12h44_25.png


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

 

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘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