How to get distinct value over overlaping period while filtering on one year

[KXuan]

Hello everyone,

I need you help, I've been trying to solve this problem for several days and still don't know what to do.

I have a contract with 2 rows in my fact table, the first line is when the contract beginns and the second line is the extension. Both have the same price of 17,99Euro. According to bussiness definition , if I filter on 2019, I need to get the price 17,99 once in total, because the price should be considered only once in one year (2019).

And if I do not filter, then I should get the sume of those, since I'm querying the entire period. In this case, my measure works.  Has anyone faced this problem, or has idea how to solve this with dax ?

Here is my measure :

var minDate = [minSelectedDate (Date)]

var maxDate = [maxSelectedDate (Date)]

return

CALCULATE(sum( '(F) Contract'[Price])

                ,   '(F) Contract'[Begin]<= maxDate &&  '(F) Contract'[End] >=minDate

        )

Thanks,

Xuan

[KXuan · Accepted solution]

I solved that problem by using summarize:)

[v-mengmli-msft]

Hi @KXuan ,

Do you want to filter the table when year is selected in slicer? For example, when user selects 2019, the first row will be filtered.

Please modify your original Dax expression as following.

Measure  =
  var minyear= MIN('(F) Contract' [Beigin])
  var maxyear= MAX('(F) Contract' [End])
  RETURN
  IF(SELECTEDVALUE(Table2[Year]) <> BLANK() , IF(SELECTEDVALUE(Table2[Year]) > YEAR(minyear) && SELECTEDVALUE(Table2[Year]) <= YEAR(maxyear) , 1 , 0) , 3)

Then set Filter as following.

Here is my test for your reference.

Best regards,

Mengmeng Li

[KXuan]

Hello Mengmeng ,

Unfortunately, it's not what I want :(. Actually ,I just want to have the correct total without filtering in the table as in you example. 

The idea of this measure is to get the price of contract which is valid in selected period ( so I think _maxyear und _minyear should be based on seperate date dimension instead ).  In this example, it's 17,99 for 2019 or 2020 and 35,98 for the entire period.

Hope, that I explaned it well 😞

Best,

Xuan