Peculiar differences in cumulative sum measures - trying to get cumulative profit by project

[JohnYeldham]

I have developed a measure for profit (SUM(revenue) - SUM(cost), as expected) that works just fine.  As expected, the measure aggregates according to the aggregate rows in output tables (e.g. if Project and Period is shown then the measure sums over Project and Period, but if only Period is shown then the measures sums over Period only).

The issue is that a measure I have made for CUMULATIVE profit does not work in the same consistent manner.  The columns in output tables affect how it works, yielding different - and wrong - results depending on columns chosen.

I have tried two different measures as  follows:

Cumulative Profit (with project constraint)

Cumulative 1 = CALCULATE([Profit], FILTER(ALLSELECTED(Table), Table[Period]<=MAX(Table[Period])))

Cumulative Profit (no project constraint)

Cumulative 2 = CALCULATE([Profit], FILTER(ALLSELECTED(Table), Table[Period]<=MAX(Table[Period]) && Table[Project] = MAX(Table[Project])))

Whether each gives the right answer depends on whether the output table shows project or not.  These mock-ups show the behaviour:

Correct (desired) cumulative balances are in green, wrong answers are in red. 

It is weird that one solution works when the Project is a column in the output table, and one solution works when it is not.  For the Profit measure, it works whatever the columns in the output table.

Anyone know how to fix it?

Cheers

John

[amitchandak]

@JohnYeldham , refer if this can help

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[Greg_Deckler]

@JohnYeldham - Would love to have some sample data to test with in order to see if I could come up with a solution that works for both. Can you post some sample data?

Overall though, I am not necessarily suprised, measures are finicky when it comes to context. Just think about the measures total problem. You could put them into a single measure like this:

Cumulative Profit =
  IF(
    ISINSCOPE('Table'[Project]),
    [Cumulative 1],
    [Cumulative 2]
  )