Dynamic Variance Based on Two Date Tables

[SEMattis]

Hi, 

I've spent some time in trying to develop a script that would allow me to create a dynamic variance calculation based on dates from two different tables. The code looks as follows:

M_%_VARI =

VAR BaseDate = EOMONTH(SELECTEDVALUE(dim_Date[EndofMonth]),0)

VAR CompDate = EOMONTH(SELECTEDVALUE(dim_ComparisonDate[EndofMonth]),0)

VAR BaseCount = CALCULATE(COUNTA(fact_CoworkerHistory[EmployeeNumber]),

BaseDate = fact_CoworkerHistory[LastDayOfMonth]

)

VAR CompCount = CALCULATE(COUNTA(fact_CoworkerHistory[EmployeeNumber]),

FILTER(fact_CoworkerHistory,fact_CoworkerHistory[LastDayOfMonth] = CompDate)

)

RETURN

CALCULATE((BaseCount - CompCount) / BaseCount)

The problem I'm encountering is that the syntax is returning blanks for both the BaseCount and the CompCount which means the variance is also returned as blank. What am I getting wrong here :S ?

Many thanks in advance.

[lbendlin]

Sounds very similar to a YoY calculation (apart from the sign).  Could you show the data model?

Would you mind showing some sample data and indicate the expected outcome based on that sample data?

[SEMattis]

@lbendlin 

Sure, here's a mock up of the data model:

DM

So the expected outcome is to be able to calculate the variance of the number of rows in the fact_CoworkerHistory (Using a snapshot date column) table depending on which of the date tables I use to filter the no of rows.

[lbendlin · Accepted solution]

You will need to use ISFILTERED or ISCROSSFILTERED to figure out which of the date tables is filtered.  What if they both are? Worst case you may have to use inactive relationships or disconnected tables.

[SEMattis]

@lbendlin many thanks. I'll go ahead and try you proposals