Forum Discussion
Need help summing values based on different columns
Hello Community! I am hoping someone can help on this today (as it's needed for Monday).
I've provided an example data set (below), and my current attempt at what I am trying to do. The requirement is this:
I need to filter our orders which have a status of "P" or "O" - that represents our backlogs. That part is easy.
Then I need to sum up this value based on two potential dates. If the commit date is blank, I need to instead use the due date instead. However, if the commit date is not blank, then use the commit date. The committ date is connected to my date table as the primary, and the due date is an inactive relationship.
The critical part is that if I put the month/year in columns in matrix table, with the "backlog value" as the measure, I need it to properly sum up the values based on the conditions above. I was experimenting below and if I just use the duedatevalue variable, it seems to only return values that match the commit date, which I don't understand since I am using USERELATIONSHIP. But the main point is that I need to use the logic above.
Backlog Value =
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
3 Replies
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
