Set analysis based on date selection

[Anonymous]

Hello,

I want to do calculations based on a date selection and I cant figure it out how to write the set analysis

I want to calculate the sum of PickQty based on the date selection in the master calendar.

%DateKey is equal to ExpectedShipDate due to the point of view (what is due on the selected date)

Basically I want to see: (assuming selection on %DateKey is today)

1. what have I done in the past that was actually due today

2. what have I done today that was due today

3. what have I done today that was due in the future

for example:

1. sum(PickQty) where PickEvent = WORKLOAD and "PickDate smaller than selected %Datekey"

2. sum(PickQty) where PickEvent = WORKLOAD and "PickDate equal to selected %Datekey"

3. sum(PickQty) where PickEvent = WORKLOAD and "ExpectedShipDate larger than selected %Datekey and PickDate equal to selected %Datekey"

One issue is the date format.

%DateKey is a regular date format but the others, PickDate and ExpectedShipDate are YYYYMMDD

I tried using a variable to work around but this makes the syntax in the set analysis even harder for me.

any help is apreciated.

attached a sample QVW with what I have tried so far.

Lucas

[Anonymous]

I'll play a little with the MasterCalendar to find out what you mean.

Thanks for your effort!

[maryshea]

I am simply trying to pull the quanity oh hand value on a past date for an item.

I am trying sum({$<[ItemHistory.Txn Date]={"<=2020-01-31"}>}[ItemHistory.Quantity On Hand]) but it is not returning the correct quantity and does not change values with a change of date.

Do you have thoughts on a solution?