Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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, 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
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
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
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 ) )
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 ) )
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |