Forum Discussion

Gaga_Jin's avatar
Gaga_Jin
Frequent Visitor
1 year ago
Solved

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, the...
  • Anonymous's avatar
    Anonymous
    1 year ago

    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)*5000000

     

     

     

    Best regards,

    Mengmeng Li