Forum Discussion

SEMattis's avatar
SEMattis
Advocate III
4 years ago
Solved

Dynamic Variance Based on Two Date Tables

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.
Need Help
Tutorial Requests
  • lbendlin's avatar
    lbendlin
    4 years ago

    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.

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    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's avatar
      SEMattis
      Advocate III
      4 years ago

      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's avatar
        lbendlin
        Super User
        4 years ago

        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.