Hi Alison,

If you don't need to use any of the Time Intelligence functions and your reporting period is in line with the calendar year, i.e. not a fiscal period, then you can simply use the in built date hierarchy, which means you don't need the Calendar table at all.

To do this, add the date you want to see in your chart to the X axis. It will automatically have a hierarchy applied for Month, Quarter and Year. Remove 'Quarter' if you don't need it.

Alternatively, if you need the calendar table then you can write your measure like so to force it to use a specific relationship (note: I've guessed the column names in the formula below):

Tot.Contract := 

CALCULATE( 

   SUM(ContractTable[ContractAmount] ), 

   USERELATIONSHIP(

          ContractTable[StartDate], 

          Calendar[DateKey] 

      ) 

)

Thank you

Using the syntax with userelationship

CALCULATE(

   SUM(ContractTable[ContractAmount] ),

   USERELATIONSHIP(

          ContractTable[StartDate],

          Calendar[DateKey]

      )

)

How would I filter the result to a given year in the date table?

I'm trying to calculate the sum of contractamount for a given fiscalyear based on the relationship ContractTable[StartDate] --> Calendar[DateKey]

I've tried including FILTER(ContractTable,RELATED(Calendar[FiscalYear])=2017 but that doesn't seem to work

Hi Alison,

You don't need to use FILTER because CALCULATE's arguments are filters. You should be able to write your measure like this:

Tot.Contract2017: 

=CALCULATE(

  SUM(ContractTable[ContractAmount]), 

  USERELATIONSHIP(

   ContractTable[Start Date], 

   'Calendar'[DateKey]),

   'Calendar'[FiscalYear],'Calendar'[FiscalYear]=2017
)

See file attached.

Note: in future please provide more detail surrounding your question and or a sample file so I can answer your question more quickly. Details that weren't clear were whether you wanted to filter in context or explicitly filter in the measure. In the file I've attached you'll see both results are returned.

Thanks,

Mynda