Where to start to count historical date ranges on large volumes

Power BI
1

Hi,

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.

Thanks,
Bee

PS. Below are 2 files - 1 with requirements/overview + .pbix file so far based on sample of 3 member accounts data

MEMBERSHIP TALLY Retrospective Analysis - Enterprise DNA Support Ticket.docx (136.0 KB)

Member Benefit Activity v7.25_EnterpriseVersionSample.pbix (688.2 KB)

NB:- Please ignore the 3 _calculation tables, which contain various attempts at tackling this …

2

Hi there, thanks for your post.

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.

https://forum.enterprisedna.co/t/rules-and-tips-for-asking-support-questions-on-the-forum/30

I have though had a look into this and here are some ideas and techniques to work through which solve this.

First your model looks good.

image
image1015×532 31.6 KB

Especially the inactive relationships here.

I’ve also looked through your formulas and don’t see the ‘events in progress’ pattern anywhere

This is what you need to calculate between two dates in your fact table.

This is covered here as well,

This formula technique is what you need for this to get going.

For example

Joined Total = 
CALCULATE( COUNTROWS( Benefits ),
    FILTER( VALUES( Benefits[Benefit Start Date] ), Benefits[Benefit Start Date] <= MAX( Dates[Date] ) ),
    FILTER( VALUES( Benefits[Benefit End Date] ), Benefits[Benefit End Date] >= MIN( Dates[Date] ) )
)

image
image898×193 11.4 KB

Start with this and see if it starts getting you somewhere. I believe it should be what I’ve seen.

Thanks
Sam

1 Like
3

Hi,

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

Thanks again,
B :smile:

 **Total Members** =
VAR AsOfDate =
    MAX ( 'Dates'[Date] ) // As of this point in time i.e., largest date in context
RETURN
    CALCULATE (
        COUNTROWS ( 'Benefits' ) + 0,
        FILTER (
            'Benefits',
            ( '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
                )
        )
    )
4

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.

https://portal.enterprisedna.co/courses/108877/lectures/2000640

Have you got the answer to totals members that you needed for now?

Sam

6

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.

Thanks again,
Bee

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
RETURN
    CALCULATE (
        [Benefit Count] + 0,
        FILTER (
            VALUES ( Benefits[Benefit Start Date] ),
            Benefits[Benefit Start Date] <= AsOfDate
        ),
        FILTER (
            VALUES ( Benefits[Benefit End Date] ),
            Benefits[Benefit End Date] >= AsOfDate
                || ISBLANK ( Benefits[Benefit End Date] )
        )
    )