Forum Discussion
Need help summing values based on different columns
Hi , Anonymous
According to your description, you want to use the Year_month as column and get the sum of the [Net Price] based on two columns.
Here are the steps you can refer to :
(1)My test data is the same as your provided.(2) I create a calendar Table like your said.
(3)We can create a measure like this:
Measure = var _date = MAX('Dim_Date'[Date]) var _t = FILTER( ALL('Table') , OR( YEAR( 'Table'[Due Date]) = YEAR( _date) && MONTH( 'Table'[Due Date]) = MONTH(_date ) , YEAR( 'Table'[Commit Date]) = YEAR( _date) && MONTH( 'Table'[Commit Date]) = MONTH(_date ) )) return IF(_date<> BLANK() , SUMX(_t , [Net Price]) )Then we can put it on the visual and we can get the result as follows:
If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).
https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...
This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.
I cannot supply the file, but the model (the relevant part) is very simple. As I mentioned, the commit date is in an active relationship, and the due date is inactive.
| Date Table Date | Commit Date | Due Date | Net Price (order value) | Backlog Measure |
| 09/20/2023 | 09/20/2023 | 35.00 | 35.00 | |
| 09/25/2023 | 09/17/2023 | 20.00 | 20.00 | |
| 09/27/2023 | 08/15/2023 | 90.00 | 90.00 |
Using the example, above, in a matrix table with the month/year as the columns, I would want this output:
August 2023 September 2023
90.00 55.00
This is the part that does not seem to work is getting the August due date value to be allocated to August (based on using the month / year from the date table).
Hi , Anonymous
According to your description, you want to use the Year_month as column and get the sum of the [Net Price] based on two columns.
Here are the steps you can refer to :
(1)My test data is the same as your provided.(2) I create a calendar Table like your said.
(3)We can create a measure like this:
Measure = var _date = MAX('Dim_Date'[Date]) var _t = FILTER( ALL('Table') , OR( YEAR( 'Table'[Due Date]) = YEAR( _date) && MONTH( 'Table'[Due Date]) = MONTH(_date ) , YEAR( 'Table'[Commit Date]) = YEAR( _date) && MONTH( 'Table'[Commit Date]) = MONTH(_date ) )) return IF(_date<> BLANK() , SUMX(_t , [Net Price]) )Then we can put it on the visual and we can get the result as follows:
If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )
Thank you for your time and sharing, and thank you for your support and understanding of PowerBI!
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly