Calculated measure - compare date dimension members with constant(filter value)

• Kiivik

  Grasshopper

  Points: 12

  More actions

  May 1, 2019 at 2:47 pm

  #3601682

  I have one date dimension "Report Date" and another date dimension "Dep Date" in the cube. First one is not connected to the sales measure and the other one is. Both have hierarchy Y-Q-M-D.

  On pivot table measure "Sales" is presented by Dep Date months. Dep Date and Report Date are both selected in filters.

  Report Date.Y-Q-M-D: 2019-02-10 (it's Feb 10th)

  Dep Date.Y-Q-M-D: 2019-Q1

  Year Month    Sales          Sales Selected Date

  Jan 2019       115652       115652

  Feb 2019       106386       36725

  Mar 2019       140548

  I need to get a calculated measure "Sales Selected Date" which is Sales amount until "Report Date" date (constant, set by user in filter). User always selects only one value in Report Date filter. For example if user selects Report Date=10th February 2019 then on the row "Jan 2019" Sales Selected Date must equal Sales, on the fow "Feb 2019" Sales Selected Date must equal sum(Sales) only until the 10th of Feb, on the row "Mar 2019" Sales Selected Date must be empty.

  Generally I need to take selected value from Report Date and use it to filter Dep Date.

  How to create the calculated measure "Sales Selected Date"?

  Thnx!