Summarizing and Calculating values based on date ranges from slicer using DAX


#1

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.

  1. Date slicer
  2. 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”
  3. Summarize [paid] column till the date selected. This date could be either from dt and to date. ==> Could create measures for this.
  4. This step is tricky part. Capture the value of [Cost] for the max of [Transdt] + [SrNo] + [TransDt]<=
    ** 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
image

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?


#2

I believe that what you want to accomplish is possible.

Can you send the pbix file with some non-proprietary data in it so I can take a look at the model and what you have done so far.

Guy

Enterprise%20DNA%20Expert%20-%20Small


#3

Thanks Guy for your reply.
Also a quick info: since i could not achieve step 2, created few measures based on main table directly.

Should I upload the file here or send it to you separately?


#4

If there I no data that’s proprietary here is okay. If you feel better sending it to me at my personal e-mail that’s okay.

Your choice.

Guy

Enterprise%20DNA%20Expert%20-%20Small


#6

It seems that you are having issues with Time Intelligence DAX formulas. Assuming the Data Model Relationships are good try reviewing these videos.

If you need help with the Data Model review these videos - in particular the Data Modeling area.

Remember, as Sam says, the best results start with a good model.

Guy

Enterprise%20DNA%20Expert%20-%20Small