I have two tables. One main data table and a date table. The date field on Date Table is tied to Trans Dt on Main Table.
Here is what I planning to do.
- Date slicer
- create two different dynamic datasets using main table as base and [from date] and [to date] from the slicer Dataset 1 should have all the data with [transdt] less than or equal to “From Dt”
Dataset 2 should have all the data with ]transdt] less than or equal to “To Dt” - Summarize [paid] column till the date selected. This date could be either from dt and to date. ==> Could create measures for this.
- This step is tricky part. Capture the value of [Cost] for the max of [Transdt] + [SrNo] + [TransDt]<= [date field**]
** date field is either the from dt or to dt from the slicer
Scenario (i) only one record for the above mentioned combination, then select the corresponding [Cost] value
Scenario (ii) if more than one record exist for the above mentioned combination, then tie-breaker filed would be [Transid] in addition to above combination.
– If the [TransID] is same for all the records, then corresponding values of [Cost] need to be summed up.
–if the [TransId] is different for all the records, then use Max[UniqueKey] between these records and then obtain the corresponding value from [Cost]
Main Table
Example scenario for Step 4 using From and to date are 1/1/2016 and 8/4/2018 including End Goal
Question
(i) is it possible to achieve step 2?
(ii) Need some guidance on step 4?
(iii) is there any appraoch to get to End goal?
