Total of Differences in Cumulative Functions

[ruthhacche]

I am calculating a running debtor forecast as follows:

1. Active relationship between calendar and sales using sale date (as normal)
2. Inactive relationship between calendar and sales using payment date
3. Debtor based on cumulative sales (as normal) less cumulative sales using USERELATIONSHIP on the payment date.

Works nicely. I then deduct my insurance cover (a fixed amount of £150k on the account below) to calculate my uninsured debt each month. Again, works nicely but of course will never total up as it is the difference between two cumulatives. But I would love to know the total of the two uninsured amounts. In my head it needs to calculate an amount filtered for each month and then al those up - is this going to be a massive piece of dax with a section filtered for each calendar month?

 

[GraH]

Could it be something like

=CALCULATE([insurance cover insufficient], allselected(fiscalmonthname))

Then use a new measure that checks if fiscalmonthname is filtered. If so, return the measure you already have, if not return the new one.
You might need to tweak this as I can't test this myself without excel.

 

[ruthhacche]

Could it be something like

=CALCULATE([insurance cover insufficient], allselected(fiscalmonthname))

Then use a new measure that checks if fiscalmonthname is filtered. If so, return the measure you already have, if not return the new one.
You might need to tweak this as I can't test this myself without excel.

Thank you. The above would not give me a total for the year because the formula only ever relates to a single month so the year total will be blank unfortunately. I ended up with 12 vars - one for each month filtered for that month and then added them up. Seems a bit heath robinson but runs pretty quick and will suffice until I become more intelligent!