The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
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 !
Solved! Go to Solution.
Hi,
Here is an example of working DAX that does similar thing:
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!
Proud to be a Super User!
Hi,
Here is an example of working DAX that does similar thing:
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!
Proud to be a Super User!
It works, many thanks 🙂
User | Count |
---|---|
28 | |
11 | |
8 | |
6 | |
5 |
User | Count |
---|---|
35 | |
14 | |
12 | |
9 | |
7 |