This is my first post since joining so hoping this is right place. I am hoping to get some advise on how to tackle the attached request.
I’ve spent a bit of time already but at point now due to the high volumes in table needed some advise on best approach for member opening/movements/closing balance over historical dates plus “engagement” filtering.
Appreciate any help you can provide and please let me know if I should submit this somewhere else.
PS. Below are 2 files - 1 with requirements/overview + .pbix file so far based on sample of 3 member accounts data
There’s a lot of detail here. We do our best to provide as much support as possible but look to stay away from consulting work which this really has ventured into. This is mainly to do with the complexity of the request.
For more information on this see here.
I have though had a look into this and here are some ideas and techniques to work through which solve this.
I had really hit a wall when I raised this post and completely understand your stance so all good, however I really appreciate the feedback you did provide.
Since posting I have now started to apply logic as per your date videos but just customized to retrieve based on this point in time otherwise I would end up with duplicates per account.
Below is example of one of the formulas I have setup and is working, so I will keep branching out to hopefully get all what I am needing.
One question though I noticed a difference with your formula is that you are using VALUES (Table[field]) in the Filter - is this better than doing Filter (Table) ?
i.e., I’m still learning when is best to do one way over the other and wondering if I should change to use your pattern to help performance of my formula
**Total Members** =
VAR AsOfDate =
MAX ( 'Dates'[Date] ) // As of this point in time i.e., largest date in context
COUNTROWS ( 'Benefits' ) + 0,
( 'Benefits'[Benefit Start Date] <= AsOfDate
&& 'Benefits'[Benefit End Date] >= AsOfDate )
'Benefits'[Benefit Start Date] <= AsOfDate
&& ISBLANK ( 'Benefits'[Benefit End Date] ) // no end date if its a "Free" one i.e., indefinite
In this case I don’t believe it matters, but I think it’s a good habit, because you actually are looking through this particular date.
VALUES creates a virtual table and list each unique value from a column. In this case it’s ok because there are likely different dates of different rows, but sometimes you might want to aggregate up differently and that’s where values comes in.
See here for more info on this function.
Have you got the answer to totals members that you needed for now?
Excellent thank you, I have now reworked my formula using values and believe it is working now. Appreciate your help and good to close this item now.
Member Count - All v2 =
VAR AsOfDate =
MAX ( 'Dates'[Date] ) // As of this point in time being the largest date in context
// NB:- Don't use MIN/MAX since need at this point in time otherwise multiples return per account
[Benefit Count] + 0,
VALUES ( Benefits[Benefit Start Date] ),
Benefits[Benefit Start Date] <= AsOfDate
VALUES ( Benefits[Benefit End Date] ),
Benefits[Benefit End Date] >= AsOfDate
|| ISBLANK ( Benefits[Benefit End Date] )