Including Dimensions in Set expression

[#1]

Hello,

I am looking for Set expression which will consider the last column to calculate the Nr column. In the example below the value

Aus-Oct2016 should be 8. It is being shown as 14 here due to the entries in one of the Fact tables. the SITE_DT column is from a different Fact table and i want the Nr to calculate the days from the Site_DT.

Please let me know if more information is needed.

Nr:

Max({<DT_CONS_DIM.FISC_YR_NBR={"$(=Only({1}YearofToday))"},

DateNum={"<=$(=Num(Max(INV_RPT_TRNS.SITE_DT)))"}>}DT_CONS_DIM.FISC_DAY_OF_MTH_NBR)

Period	CTRY_NAM	Dr	Nr	Date(Max(INV_RPT_TRNS.SITE_DT))
Oct-16	Australia	28	14	10/9/2016
Oct-16	Canada	28	8	10/9/2016
Oct-16	Germany	28	15	10/16/2016
Oct-16	IN	28	15	10/16/2016
Oct-16	India	28	14
Oct-16	Mexico	28	15	10/16/2016
Oct-16	Netherlands	28	8	10/9/2016
Oct-16	UN	28	8	10/9/2016
Oct-16	US	28	15	10/16/2016
Oct-16		28
Sep-16	Australia	35	35	9/25/2016
Sep-16	Canada	35	29	9/25/2016
Sep-16	Germany	35	34	9/25/2016
Sep-16	IN	35	29	9/25/2016
Sep-16	India	35	35
Sep-16	Mexico	35	34	9/25/2016
Sep-16	Netherlands	35	29	9/25/2016
Sep-16	UN	35	29	9/25/2016
Sep-16	US	35	35	9/25/2016
Sep-16		35
Aug-16	Canada	28	22	8/21/2016
Aug-16	Germany	28	22	8/21/2016
Aug-16	IN	28	22	8/21/2016
Aug-16	Mexico	28	22	8/21/2016
Aug-16	UN	28	22	8/21/2016
Aug-16	US	28	28	8/21/2016
Aug-16		28

Thanks.

[#2]

Thanks for your reply Ruben.My bad, let me upload the one with data. But I am using the number format for dates,

=Sum({<DateNum={"<=$(=Num(Max(INV_RPT_TRNS.SITE_DT)))"}>}SLS_RPT_TRNS.COST_USD_AMT)

My requirement is to restrict the Sum(SLS_RPT_TRNS.COST_USD_AMT) to the dates SITE_DT but it is being shown for the entire period now.

For Australia, the value should be 12533601 which is the value from the beginning of Oct2016 till 8th oct, but it being shown as 307899808 which is the value for the entire month of Oct.