Find Nearest Older Date and Sum the Amount

[daxnebie]

Hi Guys, so I have two simple tables:

DimTable Discount:

Date	Amount
2019-01-01	10%
2020-01-01	20%
2021-01-01	30%

FactTable Sales:

Date	Amount
2020-01-02	20

So the idea is to find the nearest older date based on the date of FactTable Sales, so step-by-step it would be like this:

FactTable Sales	FactTable Sales	DimTable Discount	DimTable Discount	Measures
Date	Amount	Date	Amount	Measures
2020-01-02	20	2020-01-01	20%	16

20 * (1-20%) = 16

The end product is bar chart compose of full price and discount price, per year. 

I understand that it's a very simple dataset, but the real data is more complicated than this. The discount rate changes daily.

Both tables are linked to a Date Table.

I have tried measures and calculated column but nothing works. The measure always return 10% instead of 20%. Do you guys can help me? 

My code for calculated column is below and this return 2019 which is 10%, instead of 2020, which is 20%.

SurveyDate = 
CALCULATE ( 
    MIN(Discount[Date]),
   FILTER ( 
        Discount,
        Discount[date] >= related(Sales[date])
    ) 
)

For measures, I even tried to use summarizecolumns, but that doesn't work either because of the filtering issues which I still do not understand why.

Thanks guys for your help! Really appriciate it

[tamerj1]

Hi @daxnebie 

I undestand that your problem comes from the gaps in dates in the dim. table. 
in this case the I would suggest to workt out in power query. You can easily create a date table in power query from first date to last date on the fact table. Then you can merge the dim table with the date table (left outer join). Keep only the complete dates column and then "down fill" the Amount column. 

[amitchandak · Accepted solution]

@daxnebie , Create a new column like

new column =
var _max = maxx(filter(discount, discount[Date]<= Sales[DAte]), discount[date])
var _min = minx(filter(discount, discount[Date]> Sales[DAte]), discount[date])
var _diff1 = datediff(_max, ,Sales[DAte], day)
var _diff2 = datediff(Sales[DAte],_min, day)
var _date = Switch(true() , _diff1 > _diff2, _min , _max)
return
sales[Amount] * (1- maxx(filter(discount, discount[Date]= _date), discount[Amount]))