topic Re: SPLY with Multiple Date Columns in DAX Commands and Tips

SPLY with Multiple Date Columns

NilR — Thu, 02 May 2024 12:48:13 GMT

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!

Re: SPLY with Multiple Date Columns

DataNinja777 — Thu, 02 May 2024 13:12:56 GMT

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,

Re: SPLY with Multiple Date Columns

NilR — Thu, 02 May 2024 14:51:39 GMT

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?