Set Analysis built before/after July 1 of year in dimension

[mjtaft2017]

The requirement - of the items sold in a given month/year, give a count of those built before July 1 of that year and those built on/after July 1 of that year.  (Sold Date and Built Date are identified on common calendar by Date Type).  So in my set analysis I am looking at the BuiltDate of the records of DateType ='Sold'. The Fact table contains the itemKey, Sales Code, Built Date, Sold Date, BuiltDateNullFlag  (for those items sold but built prior to 2017)

Dimension is MonthYear (see table below).   This worked correctly with hardcoded values, but now I have 2 years of data 2017, 2018 and I want the year to be the year in the dimension.  When the MonthYear is  Aug-2017, I want to get the count of items sold in Aug-2017 that were built before July 1 2017 and those built on/after July 1 2017.   I don't want the user to have to select a year .   

Before July 1 :

COUNT({<DateType={'Sold'},[Sales Code]={'ABC'},BuiltDate={"<2018-07-01"}>+<DateType={'Sold'},[Sales Code]={'ABC'},BuiltDateNullFlag={'1'}>}DISTINCT [itemKey])

On/After July 1:

COUNT({<DateType={'Sold'},[Sales Code]={'RSK'},BuiltDate={">=2018-07-01"}>}DISTINCT [itemKey])

[sunny_talwar]

Why don't you create a new field which is just a Month (and/or MonthNum) field and then use that. For example if you create a field like this

LOAD BuiltDate,
   Num(Month(BuiltDate)) as BuiltMonthNum
...
FROM ....

and then use this

Before July 1 :

Count({<DateType = {'Sold'}, [Sales Code] = {'ABC'}, BuiltMonthNum = {"<7"}>+<DateType = {'Sold'}, [Sales Code] ={'ABC'}, BuiltDateNullFlag = {'1'}>} DISTINCT [itemKey])

On/After July 1:

Count({<DateType = {'Sold'}, [Sales Code] = {'RSK'}, BuiltMonthNum = {">=7"}>} DISTINCT [itemKey])

[mjtaft2017 · Accepted solution]

That worked - thank you!!

[sunny_talwar]

Super 🙂