Forum Discussion

jonathansharman's avatar
jonathansharman
Regular Visitor
5 years ago

Filter MAX Date in CALCULATETABLE - Working with hard coded dates, not working using MAX.

Hi All,

 

I am trying to CALCULATETABLE on a MAX date from a report slicer to give a result set that can be explored and the code is shown below.  When hard-coding the date into VAR MaxDate I get the results in the report I want and expect.  However when I change this to use MAX(ZZ_CalendarDimension[Date]) with a slicer set to the month of January 2021 the result set is incorrect.  The tables are not related in the background as I am looking to determine open items at the start of period that may be different to the Month we are exploring in the report hence the VAR EarliestDate.  This will become the MaxDate - 365 days once this issue is resolved.

 

Any hints greatly appriciated!  ðŸ˜€

 

ZZ_VM_openleads =
VAR EarliestDate = DATE(2021,1,1)
VAR MaxDate = DATE(2021,1,31)
RETURN
CALCULATETABLE(
SUMMARIZE(VM_10_enquiry,
VM_10_enquiry[VM_EN_ENQUIRY],
VM_10_enquiry[VM_EN_CREATIONDATE],
VM_10_enquiry[VM_EN_CONTACT],
VM_10_enquiry[VM_EN_CONTMETH],
VM_10_enquiry[VM_EN_BRANCH],
VM_10_enquiry[VM_EN_ENQTYPE],
VM_10_enquiry[VM_EN_LOSTSALE],
VM_10_enquiry[VM_EN_LOSTSALEDATE],
VM_10_enquiry[VM_EN_ORIGCONFRMDATE],
VM_00_qualify_preference[VM_QC_BRAND],
VM_00_qualify_preference[VM_QC_MODEL],
VM_00_qualify_preference[VM_QU_VEHTYPE]),
VM_10_enquiry[VM_EN_CREATIONDATE] >= EarliestDate,
VM_10_enquiry[VM_EN_LOSTSALEDATE] >= MaxDate || ISBLANK(VM_10_enquiry[VM_EN_LOSTSALEDATE]),
VM_10_enquiry[VM_EN_ORIGCONFRMDATE] >= MaxDate || ISBLANK(VM_10_enquiry[VM_EN_ORIGCONFRMDATE]))
Need Help

1 Reply

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    5 years ago

    Hi jonathansharman ,

     

    Measures cannot be used on table in your model meaning that your filtering on table will not be applicable.

     

    Measures are based on context and that can change depending on visualizations, filters, slicers, columns used and other different aspectes, tables are calculated based on row context, this means that you cannot get the row context from a measure that can assume multiple values.

     

    In this case you need to create your table within a measure because measures can be used inside other measures to make your calculation that way you can have a measure working within your table context to give you the result you need.