Relative Period Filter

[DickyMoo]

Hi All,

I am using a disconnected copy of my Date table as a filter in a measure. The user selects a FinancialPeriod from the disconnected table and the measure is shown in a table with rows of FinancialPeriod from the original Date table.

The purpose of the measure is a kind of 'Where are they now?', and takes a group of Affiliates at the selected date and shows how many of them convert to Members etc over time.

This works fine. What I am trying to do now is to have multiple tables each with different start periods, each showing the next 24 FinancialPeriods, so I can show what happens to a group of people over time, with different start points.

Any ideas on how to do this? I don't think Relative Date Filtering is right, but can I use a measure to select the periods.

FYI our FinancialPeriods are 2018001, 2018002 ... 2018012, 2019001, 2019002 etc so I want the (disconnected) selected FinancialPeriod + 2000.

The measure is shown below for reference:

[COLOR=#000000][FONT=Consolas]Count - Affiliates Then Members Now = 
// The count of Current Active Members who were Affiliates in the Financial Period selected in Dim_Calendar_Copy
VAR varPeriod = MAX([B]Dim_Calendar_Copy[Financial Period][/B])
VAR varThen =
    CALCULATETABLE (
        VALUES('FEE ANALYSIS'[Member Number]),
        'FEE ANALYSIS'[Member Status - Current] = "Active",
        Dim_MemberCategory[Headline Category] = "Affiliate",
        FILTER(ALL(Dim_Calendar), Dim_Calendar[Financial Period] = varPeriod)
    )
VAR varNow =
    CALCULATETABLE (
        VALUES('FEE ANALYSIS'[Member Number]),
        'FEE ANALYSIS'[Member Status - Current] = "Active",
        Dim_MemberCategory[Headline Category] = "Member",
        Dim_Calendar[Financial Period] > varPeriod
    )
VAR Result_1 = IF( HASONEVALUE(Dim_Calendar[Financial Period]), COUNTROWS ( INTERSECT ( varThen, varNow ) ), BLANK() )
RETURN Result_1
[/FONT][/COLOR]

sorry if this is a bit confusing

Thanks
Richard

 

[DickyMoo]

I've answered my own question, used a measure:

[COLOR=#000000][FONT=Consolas]FinancialPeriod_Filter = 
IF (
    AND (
        MAX ( Dim_Calendar[Financial Period] )
            >  MAX ( Dim_Calendar_Copy[Financial Period] ),
        MAX ( Dim_Calendar[Financial Period] )
            <= MAX ( Dim_Calendar_Copy[Financial Period] ) + 2000
    ),
    1,
    0
)
[/FONT][/COLOR]

and applied a TopN filter, top 1 by this measure.

Seems to work ok, better methods would be welcome.

Richard

 

[RicoS]

and applied a TopN filter, top 1 by this measure.

Seems to work ok, better methods would be welcome.

Richard

I'm not suggesting that I have a better method, but I was intrigued as to why you didn't introduce a "Became Member" date (or period) that you could use your measures on? Also, you mentioned a disconnected Date table and I wondered why you had chosen that approach? I have a similar Financial Period syntax to you and I usually create a date (all dates inclusive) table from the first to last periods in my report with a function. You could then use that date table to filter your report and then use a measure like
CALCULATE(VALUES('FEE ANALYSIS'[Member Number]),Filter(Dim_Calendar,DATEADD(Dim_Calender[Date],2,YEARS)))

Sorry it syntax errors, not at my PC! Hopefully you get the idea.

 

[DickyMoo]

I guess I went for this approach because there are multiple things that an Affiliate could do: Convert to Membership, Resign, Something else entirely etc, and I'm still working it all out.

We have lots of different paths through membership, so I felt using a measure for each path gives me flexibility at this stage. Ideally one day we get a nice data warehouse with the necessary columns showing dates at which each stage is achieved, but we're still paving the way for that.

I'm no DAX expert (DAXpert?) but I have found a method that works for me. I'm particularly keen on the COUNTROWS( INTERSECT / EXCEPT ) approach to replicate the SQL IN / NOT IN

The original date table is used as the rows on the table, and the disconnected copy is used to select the period from which you will track a group. A few of these measures then shows me how this group progresses over time, i.e. after 1 / 2 / 3 years how many are Members / Resigned etc. I need 2 period inputs so I have used 2 date tables, I can't think how else to do it.

I have no doubt that someone out there will have a better way with a bit of DAX magic.

 

[RicoS]

Yes, I see, I did wonder if there were more "phases" of an affilliate's lifecycle. You'd probably need a fact table of affiliate statuses with from and to dates or something in that case. It'd make for an interesting project for you!!!

I'm in a similar boat with DAX, I'm very much learning via trial and error and then understanding the concepts in reverse based on outcomes. I do, at times, wonder how efficient my queries are/aren't and the current environments I work in are probably quite forgiving in that regard so I'm not really testing myself to make thinks better. For instance, I thought INTERSECT and CALCULATETABLE were quite inefficient, but I think I'm wrong about that. It's good to get a new playground to play in I suppose!