Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
BaptisteP
New Member

Calculate with a different rate depending on the date

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):

BaptisteP_0-1642413886841.png

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:

BaptisteP_3-1642414826927.png

 

What I actually have:

BaptisteP_4-1642414838711.png

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 !

 

1 ACCEPTED SOLUTION
ValtteriN
Super User
Super User

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:
ValtteriN_0-1642416507460.png


End result:

ValtteriN_1-1642416519905.png


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!






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

2 REPLIES 2
ValtteriN
Super User
Super User

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:
ValtteriN_0-1642416507460.png


End result:

ValtteriN_1-1642416519905.png


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!






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




It works, many thanks 🙂

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.