Forum Discussion
SUMIF / DAX
Frankly, the output you want is a bit misleading to the viewer. What does each line represent? You could try doing a matrix instead of a table, and set your rows to have a hierarchy of Project => Accounting Period. If you do this, you'll see the amount for each accounting period after a single drilldown, as well as row subtotals that will give you the data you're after. I'm assuming the last accounting periods are supposed to be different, or those are otherwise individual line items that you can delineate with an identifier.
Thanks for the reply. I can understand your comment, that it can be misleading, however I want to use the measure to filter out all the enrties for which the projects have a 0 balance. The matrix solution unfortunately is not possible, as the user want to see all detailed lines (transactions) at once, instead of drilling up and down.
In that case, you can leverage the interactivity of Power BI. On one table or chart (for example a bar chart), you have a listing of projects with their aggregate balance. This can be done easily out of the box, and you can filter that visual to have a sum of Amount greater than zero, or not equal to zero. Once the user clicks on any projects there, a table showing the individual transactions will be filtered to that project.
If you really need to, you can use CALCULATE with a simple SUM on Amount, and a filter that compares "Project" to "EARLIER(Project)".