0

I would like to get the running sum of [Days Past Due] Column grouped by project number and for each project number, get the value for each phase/end date. IN SQL I would do: 

SELECT   Project_number
         ,phase
         ,Sum(Days Past Due) RunningTotal
FROM     Table

GROUP BY Project_number
         ,phase
ORDER BY Project_number
         ,phase

I would do something similar in DAX please.

I try that: 

CALCULATE (
    SUM ( DataSource[Days Past Due] ),
    ALLEXCEPT ( DataSource, DataSource[Project Number] )
)

It gives me the total for each project number repeated in the 4 rows. That's not what I want exactly. I need to apply a second filter on that.

Please look at the attachment, it has the last column with the desired output.

Thank you in advance for your suggestions.

enter image description here

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
Faical
  • 45
  • 7
  • Check these posts: https://stackoverflow.com/questions/40226792, https://stackoverflow.com/questions/30072341, https://stackoverflow.com/questions/18275270. You may find more by searching `[dax] running total` – Alexis Olson Apr 23 '19 at 21:13
  • Thanks Alexis for your suggestion. I have seen countless examples of that nature before I posted this question. The issue is that I can't apply two filters, I wanna say - in DAX language - Group by project number, then within each project, group by either phase of end date so I can get the running sum for each row and the one above it. and it has to reset at the beginning of each risk. I ve been struggling with that for almost a week now. Still not working. – Faical Apr 23 '19 at 21:30
  • I don't understand what the problem is. You can pass as many filters as you like inside the `CALCULATE` function. – Alexis Olson Apr 23 '19 at 21:50
  • Like if I do this:=CALCULATE(SUM(DataSource[Days Past Due]), FILTER(ALL(DataSource), DataSource[End Date] <= MAX(DataSource[End Date])), DataSource[Project Number]) .. As you can see. There is two filters, but it doesn't take them. The output is not giving the right result. – Faical Apr 23 '19 at 21:55

1 Answers1

1

You can adjust your attempt like this:

Cumulative Days Past Due = 
CALCULATE(
    SUM( DataSource[Days Past Due] ),
    FILTER(
        ALLEXCEPT( DataSource, DataSource[Project Number] ),
        DataSource[End Date] <= MAX( DataSource[End Date] )
    )
)

Note that you can include more filtering conditions in the FILTER function by joining more conditions with &&. You can add more filter conditions inside CALCULATE as well. Here's an example:

Cumulative Days Past Due = 
CALCULATE(
    SUM( DataSource[Days Past Due] ),
    FILTER(
        ALLEXCEPT( DataSource, DataSource[Project Number] ),
        DataSource[End Date] <= MAX( DataSource[End Date] )
            && DataSource[Start Date] > DATE( 2018, 12, 31 )
    ),
    DataSource[Phase] = "Scope"
)

The documentation for CALCULATE and FILTER might be useful for further clarification:

https://dax.guide/calculate/
https://learn.microsoft.com/en-us/dax/calculate-function-dax

https://dax.guide/filter/
https://learn.microsoft.com/en-us/dax/filter-function-dax

If you are trying to write this formula in a calculated column, it will not work as expected and you will need to use EARLIER instead of MAX:

Cumulative Days Past Due = 
CALCULATE(
    SUM( DataSource[Days Past Due] ),
    FILTER(
        ALLEXCEPT( DataSource, DataSource[Project Number] ),
        DataSource[End Date] <= EARLIER( DataSource[End Date] )
    )
)

The reason for this is that in a measure MAX is calculated within its local filter context but in a calculated column the context is different and you use EARLIER to refer to the earlier row context, that is, the End Date in the current row of the larger table (rather than the smaller FILTER table).

If you want something that works either as a measure or a calculated column, then @RADO's solution is pretty close and you can write this:

Cumulative Days Past Due =
VAR CurrDate =
    CALCULATE( MAX( DataSource[Start Date] ) )
RETURN
    CALCULATE(
        SUM( DataSource[Days Past Due] ),
        ALLEXCEPT( DataSource, DataSource[Project Number] ),
        DataSource[End Date] <= CurrDate
    )

In this version, the MAX works just as it did before in the first version above and it should also work in a calculated measure because wrapping the MAX in a CALCULATE performs a context transition that transforms the row context into a filter context corresponding to that single row.

You can drop the CALCULATE wrapper for just a measure and drop both the CALCULATE and MAX functions for a simpler calculated column. In either of these cases, this variable version is likely the more performant one since the ALLEXCEPT function is optimized to work efficiently within CALCULATE rather than having to instantiate a new table when using FILTER.

For further details on cumulative totals, I recommend DAX Patterns as a resource:

https://www.daxpatterns.com/cumulative-total/

Alexis Olson
  • 38,724
  • 7
  • 42
  • 64
  • Thanks for sharing that. I tried both suggestions and same issue: I get the total of 4 rows for each project Number repeated for each phase. (I took a screenshot but no way to attach it here) . Looking at the example above in the post: for first risk, it says 7 for all 4 rows, and 10 for all 4 following rows. It's not giving the running sum. – Faical Apr 23 '19 at 22:19
  • Are you doing it as a calculated column? If so, then use EARLIER instead of MAX. I wrote the above as a measure instead. – Alexis Olson Apr 23 '19 at 22:23
  • Yeaaaayyyy, I did earlier function and got the expected result. This is the final function: =CALCULATE(SUM(DataSource[Days Past Due]), FILTER(ALLEXCEPT(DataSource, DataSource[Project Number]), DataSource[End Date] <= EARLIER(DataSource[End Date]))) – Faical Apr 23 '19 at 23:34