Forum Discussion

NilR's avatar
NilR
Post Patron
2 years ago

SPLY with Multiple Date Columns

I have Service Date Column and Paid Date Column that might occures 3-6 month after the service date. So for instance I need to filter the dashboard 12 months of service and 15 months of Paid, How can I apply SPLY to the both [svc date] and [paid date] in a calculated measure [INVOICE] in a new measure!

 

VAR _SVC_MAX_DATE = MAX('SERVICE CALENDAR'[Date])
VAR _SVC_MIN_DATE= MIN('SERVICE CALENDAR'[Date])
VAR _PD_MAX_DATE = MAX('PAID CALENDAR'[Date])
VAR _PD_MIN_DATE= MIN('PAID CALENDAR'[Date])
RETURN
CALCULATE([INVOICE],SAMEPERIODLASTYEAR('INVOICE'[SVC_DT]),'INVOICE'[CLM_DT]>= _SVC_MIN_DATE && 'INVOCE'[SVC_DT] <= _CLM_MAX_DATE)

 

Thanks!

 

2 Replies

  • Hi NilR 

     

    It seems to me that you don't need to have two calendar tables in your data model.  You can just have one calendar table and create inactive relationship between your calendar table and either of the Service Date Column or Paid Date Column, and then userelationship dax function to activate the inactive relationship.  

    Best regards,

     

    • NilR's avatar
      NilR
      Post Patron

      Thank you very new to stuff you just said 🙂 is there any article or something similar that I can read about ? or giving me an example?