How to write this measure

[jackmccarthy]

In my transaction table I am trying to return the last date of a certain activity (activity 9) and all activities From that date forward of activity (10) Does anyone have some suggestion's, I'm a newbie and I am stumped...... ie: this is a cash dispensing safe. Activity 9 is the activity for fill safe and activity 10 is each dispense of the safe. I am trying to determine how much money is in safe at any given time.

 

[MD610]

This can be solved pretty easily by treating the amount of money as a running total.

To do this, Activity 9 must have the amount of money as a positive number and Activity 10 must have it as negative.

If this isn't the way it is set up in your data, then you can easily add a Calculated Column to make it that way:

I would call this new column New Amt:

=IF(
       [Activity]=9,
       [Amount], 
              IF(
                   [Activity]=10,
                   -1*[Amount]
              )
   )

Then your measure for running total is:

TotalMoney:= CALCULATE(
           SUM(TableName[New Amt],
           FILTER(
                 ALL(TableName),
                 TableName[Date]<=MAX(TableName[Date])
           )
)

In the above, I am assuming you created the new Calc Column and that all your data is stored in the same table. If you have a separate date table related back to you transaction table, then you will want to update the tables and fields listed in FILTER() to point at your date table instead.

If I understand you problem correctly, this should give you the total amount of money left in the safe at any given date.

 

[jackmccarthy]

Ok Mike..........I really had it way wrong, it's a bit more complicated then I first thought. Ok this safe dispenses 20's and 100's and fill is done once a week. Activity(9) represent the fill and activity (10) which there are 2 for each fill represents the amount of 20's and 100's that were loaded into the safe, these are in the "transaction table". The cash outs from the safe are represented in the columns (billdenom) the data will be 20 or 100 and the (dispensed qty) the data will be how many 20's or 100's have been dispensed are in the "safetransitem table" So........... I need to first determine the last time activity (9) was done and return that date and the (2) activity (10's) amounts and then subtract out how many 20's and 100's have been dispensed since that date ........as well as return the amount left in 20's and the amount left in 100's not just the combined total. Way over my head...