Forum Discussion
Dax Formula
hi, there.
This is a code that calculates a player's bonuses based on their performance. if a player is entitled to a bonus or not, is determined by "Eligible" Measure. it shows 1 or 0. if 1, then bonus should be awarded . The mechanism of operation is as follows. If a player has Eligible = 1 on the first day, then he received a certain amount (for example: 500,000), if on the second day, then he received 1,000,000, if on the third, then 1,500,000. If he messed up this sequence, he will not receive anything. However, from the next day he can continue this progress. My code cannot look at the previous day, and every day if the player's Eligible = 1 - it writes only 500,000 and does not look at the previous day's activity.
guy's you can offer our versions. will be glad.
The problem is shown on a screen.
Hi Gaga_Jin ,
You can try this method and it worked in my test.
1. Calculates the date on which the last Eligible was 0 based on the current date.
2. Calculate the date difference and multiply by 5,000,000 to get the cumulative reward.
Situations to consider are:
1. Whether the current date is the minimum date
2. There are no rows with an Eligible of 0.
Here are my test data.
Expected output:
The DAX.
Bonus = VAR current_date = MAX('Table'[Date]) VAR MinDate = MINX(ALL('Table'),'Table'[Date]) VAR Date_Table = FILTER(ALL('Table'),'Table'[Eligible]=0&&'Table'[Date]<=current_date&&'Table'[ID]=MAX('Table'[ID])) VAR last_date = IF(ISEMPTY(Date_Table),MinDate-1,CALCULATE(MAX('Table'[Date]),ALL('Table'),'Table'[Eligible]=0&&'Table'[Date]<=current_date&&'Table'[ID]=MAX('Table'[ID]))) RETURN DATEDIFF(last_date,current_date,DAY)*5000000Best regards,
Mengmeng Li
2 Replies
Hi Gaga_Jin ,
You can try this method and it worked in my test.
1. Calculates the date on which the last Eligible was 0 based on the current date.
2. Calculate the date difference and multiply by 5,000,000 to get the cumulative reward.
Situations to consider are:
1. Whether the current date is the minimum date
2. There are no rows with an Eligible of 0.
Here are my test data.
Expected output:
The DAX.
Bonus = VAR current_date = MAX('Table'[Date]) VAR MinDate = MINX(ALL('Table'),'Table'[Date]) VAR Date_Table = FILTER(ALL('Table'),'Table'[Eligible]=0&&'Table'[Date]<=current_date&&'Table'[ID]=MAX('Table'[ID])) VAR last_date = IF(ISEMPTY(Date_Table),MinDate-1,CALCULATE(MAX('Table'[Date]),ALL('Table'),'Table'[Eligible]=0&&'Table'[Date]<=current_date&&'Table'[ID]=MAX('Table'[ID]))) RETURN DATEDIFF(last_date,current_date,DAY)*5000000Best regards,
Mengmeng Li
