Octavio Finance 2d image Hello!

DAX DAX Calculations
1

2d

image
Hello!!!
As you can see my report it is almost perfect, I just need one more modification, when I pull a company with more than a year the report bring the payments since the beginning of operations with that company and calculates 6% it is correct but I need to be able to select moth and year example:
Company started date January 1, 2020 since the company started 2020 the selection will be 6%, and bring all the months, I need to be able to select example September 2021, but since the company has more than a year the selection should be 6%

2

Hi,

Thanks for posting your query .It will be grt help if your provide us the pbix it will help me or other to solve your query faster.

Thanks,
Anurag

4

This isthe Dax
Bonus % =

VAR _Time_Period =
DATEDIFF( SELECTEDVALUE( Data[Date] ) , TODAY() , YEAR )

VAR _Bonus_Percentage =
IF( _Time_Period < 1 , 0.12 , 0.06 )

RETURN
IF( ISBLANK( SUM( Data[Payment] ) ) ,
BLANK(),
_Bonus_Percentage

5

Did you try to change your intervall to months?

VAR _Time_Period =
DATEDIFF( SELECTEDVALUE( Data[Date] ) , TODAY() , MONTH )

VAR _Bonus_Percentage =
IF( _Time_Period <12 , 0.12 , 0.06 )

I think that should work, as in datediff (year) dax calculates full years. If your data only starts in Jan 20, this automatically will be only one (full) year.

Sabine

7

Sabine,
The formula is working but what I need to identified is:
The company has more than 12 months with us does not matter the year the % is 6%, if is less than 12 months the % will be 12%

8

Hi Octavio,

could you please formulate a question? I don’t understand the problem, thanks!

Sabine

9

let me see if I can ask a question:
New company = 12% for 365 days or 12 months after 365 days or 12 months =6%
Old companies = 6% more than 365 days or 12 months
Let me know if makes sense?

10

Hi Octavio,

thanks for the clarification.

In this case, please try this instead:

VAR _Time_Period =
DATEDIFF( SELECTEDVALUE( 'Date'[Date] ) , TODAY() , MONTH )

VAR _Bonus_Percentage =
IF( _Time_Period <12 , 0.12 , ((_Time_Period - 12) * 0.6 + 12 * 0.12) / _Time_Period)

RETURN

IF( ISBLANK( SUM( Data[Payment] ) ) ,
BLANK(),
_Bonus_Percentage

How does it work?

The first step is easy: Check if _Time_Period is <12, then 12%.

Second step:

  • First you have to extract the “rest” before the 12 months → _Time_Period - 12
  • Those will be given a 6% discount
  • Next you give 12% bonus for 12 months
  • Lastly you need to divide this by _Time_Periode to get the average percentage

I hope this is, what you are looking for :slightly_smiling_face:

Sabine

11

Sabine thank you so much for your help, I do not understand way is not working
VAR _Time_Period =

DATEDIFF( SELECTEDVALUE( SFACompPay[Payment Period] ) , TODAY() , MONTH )

VAR _Bonus_Percentage =

IF( _Time_Period <12 , 0.12 , ((_Time_Period - 12) * 0.6 + 12 * 0.12) / _Time_Period)

RETURN

IF( ISBLANK( SUM( SFACompPay[payment Period] ) ) ,

BLANK(),

_Bonus_Percentage

12

Hi Octavio,

in one of your previous posts you referenced the date table. Now you are referencing the SFACompTable - this could be the problem.

  • Sabine
13

Sabine
Thank you so much and the suggestion of average % was excellent!!!
Now it is working very nice
Thank you again

1 Like