need a date field to get dynamically values from canonicaldate field (set analysis)

[ioannaiogr]

Hello everyone.

I have lots of dates in my tables, so I created a canonical date  called CanonicalDate. In my date master calendar, the CanonicalDate now has a field called WholeYear which is like 2020-2021, 2021-2022 etc.

I have to calculate  a measure like: count the ids that ever had a delivery_date, a canceled_date in the selected WholeYear, a status as 'cancelled'.

I tried looking at it with 2 ways but I am not really sure:

1) 

count({<Status={'Cancelled'},delivery_date={"=(len(delivery_date)>0)"},cancelation_date={"=(len(cancelation_Date)>0)"},DateType={'Cancelled'}>} distinct ID)

using flag DateType 'Cancelled' from date bridge table.

2) Would like to know how I could do it with field selections dynamically

Created Year_cancelation_date so that I can translate WholeYear in just Year eg. 2020-2021 as 2020,2021 and then in set analysis say  Year={"=GetFieldSelections(Year_cancelation_date)"} but for when field WholeYear is selected.

In my mind I want WholeYear to be selected and my set analysis to do the calculations for split years.

I am missing the execution part.

Please, I would like a helping hand👩‍💻👨‍💻

[AustinSpivey]

1. Your set analysis is fine, it looks like you just have a casing issue here: 
   

   cancelation_date={"=(len(cancelation_Date)>0)"}
   

   That cancelation_Date should match the casing of your field, so it should be all lowercase, like cancelation_date. Also, if you're just trying to find non-null values in the cancelation_date and delivery_date fields, you can use an asterisk inside your double quotes, like: 
   

   cancelation_date={"*"}

   That's basically the same thing as saying "include all non-null [cancelation_date] values in this set."
   
   
2. Not sure I understand what you're saying here. You want to be able to select the [Year_cancelation_date] field and have that dynamically select the same values in the [Year] field? If that's the case, it seems like that would just need to be done in the data model, where you'd associate those fields together, perhaps using the [Date Bridge] table you mentioned. You may be able to achieve what you want just in the frontend by using set analysis, but you will probably get wrong/slow calculations due to expensive look-ups and possible circular dependency-like behaviors.
   
   To know for sure, we'd need to have a look at the data model and get some clarity on what it is you specifically want to achieve.