Bucketing totals across relationships

[Voose]

Hi All,

I'm looking to work out how many units that are as part of a container have been used and wether or not those units are consumed and after that to see wether or not they are expired.

Example:

In the above examples I would want to get the following results:

For project 1 55 has been consumed and assuming the date = 02/12/2017 then we know that container 1 and 2 are expired.

I would want to know that there is 25 expired amount for project 1

The next one would look at project 2 and because of the sequencing it would mean that there are 0 expired Amount

for project 3 there are 30 expired amount because the container 1 is number 3 in the sequence therefore it would have to use the other 2 containers first befoer that one and therefore I would want to know that there is 30 expired amount for Project 3

In total I have 30+25 = 55 Expired units that need dealing with and I know the associated projects and containers

I'm trying to write a measure that would achieve the above but I'm struggling 😞 I've simplified my example, in reality all of these fields are in different objects - I.E. Projects are 1 object Sequencing and Expiry are in another object and the containers and amount are a 3rd object.

Thanks for any help in advance!

Voose

[v-chuncz-msft]

@Voose,

Based on my experience, the point is to make good use of Cumulative Total.

[Voose]

Morning,

So for my understanding, we could use this:

Cumulative Quantity :=

CALCULATE (

    SUM ( Transactions[Quantity] ),

    FILTER (

        ALL ( 'Date'[Date] ),

        'Date'[Date] <= MAX ( 'Date'[Date] )

    )

)

To total all of those that are before a certain expiry date then minus that off the total amount consumed and if a negative number then ignore, if positive that thats the amount thats expired. However in the example I've given I've just given the total amount consumed, in reality there would be a bunch of lines that equal that total and each of those lines would have a certain amount that they would take away from the container, how do I ensure that these are done in the right sequence?

Thanks

Voose

[v-chuncz-msft]

@Voose,

While calculating the cumulative total, use the sequence instead of date.

[Voose]

Hi @v-chuncz-msft,

I'm still not sure I understand I still need to use the expiry date to ensure that I'm only minusing those consumed vs those that are already expired?

Thanks

Voose

[v-chuncz-msft]

@Voose,

That's the second part. It's a little bit complicated. First you need to get the remaining containers and the corresponding amount.

[Voose]

@v-chuncz-msft

Ok cool got the first part working, see below:

Cumulative Budget Amount = calculate(sum(Budget[pse__Amount__c]),filter(Budget,Budget[Budget_Sequence_Number__c]<=max(Budget[Budget_Sequence_Number__c])))

I've tested this so I know that its returning a cumulative amount per project, what would you recommend as a next step?

Thanks

Voose

[v-chuncz-msft]

@Voose,

For simplicity, add a calculated column showing remaining amount for each project container, then the measure is obvious.