Calculate with a different rate depending on the date

[BaptisteP]

Hello,

I have a table "Product" linked to a table "Dim_Calandar" used to select the date.

I need to create a measurement with the particularity that the rate in this measurement can change depending on the selected date.

Here a more speaking exemple (a transcription of what I got in my cube):

Those corrective rate are applied on the total of sales.

What I tried to do :

Total_Sold:=
    CALCULATE(SUM(Product[Number_Solded]))

Total_Sold_Corrected:=
    VAR SelectedDate = SELECTEDVALUE(Dim_Calendar[Date])
    RETURN
    IF(
	SelectedDate < DATE(2020, 2, 1),
	DIVIDE(
            Product[Total_Sold],
	    (1-0.10),
	    0
	),
	DIVIDE(
	    Product[Total_Sold],
	    (1-0.15),
	    0
	)
    )
	

What I aim to get in my pivot table:

What I actually have:

You can see that the corrective rate is always 10% and don't update depending on the date.

I also tried to apply the second formula with DATESBETWEEN, but I get an error saying I have multiple returned values instead of a single value.

Thanks for you help !

[ValtteriN · Accepted solution]

Hi,

Here is an example of working DAX that does similar thing:

Corrected = var val = SUM(Correction[Value])

var _Date = MAX('Calendar'[Date])

return

IF(_date<date(2021,2,1),divide(val,0.85),divide(val,0.90))

Data:

End result:

I recommend that you check the relationship between your fact table and calendar and additionally the columns used in your visual if you run into problems based on this example.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

[BaptisteP]

It works, many thanks 🙂