sum ignores dimension...

[#1]

Hi,

I have a the following pivot chart:

Dimensions :

1. @ProductWeeklyGroup - Cyclic group: Sub Group, Main Group, Store, Formula...

2. YearWeek - Calculated dimension showing the last X weeks from a given reference date. The reference date is last date in selection or coming weekend (the lower of the two).  Please note that here i had to make a use of date island

code:

=IF([DI Date] >=Weekstart(Date(Rangemin(WeekEnd(aggr(Max(TOTAL [DI Date]),[DI YearWeek])), WeekEnd(Today()))),($(vLookback_weeks) * -1)) and 
    [DI Date] < Date(Rangemin(WeekEnd(aggr(Max(TOTAL [DI Date]),[DI YearWeek])), WeekEnd(Today()))), 
[DI YearWeek]
)

expression:

1. Inventory - Here i aim to calculated the total inventory in the last day of the week (of YearWeek).

code:

 SUM( 
                              if(
                                  Max(TOTAL [DI Date]) >= [Inventory Date] and 
                                  Max(TOTAL [DI Date]) < [Inventory Next Date]
                                  ,
                                 [Inventory Stock Qty]
                              )
)

as imagined, this is where it goes wrong...

- Using TOTAL essentially gives the last date in the selection, regardless of the dimension. This is wrong...

- I think large part of the problem lies on the fact that while @ProductWeeklyGroup dimension  and [Inventory Stock Qty] originate from the data model, YearWeek Dimension and [DI Date] come from date island.

- Using  aggr(max([DI Date]),[DI YearWeek]) in the Inventory expression does not work

your help is greatly appericiated!

Kind regards,

Dror

[inventory stock qty]

Hico- --l

[#2]

I might be wrong, but it feeös like u have to connect your measure With lunarweek using à set analysis