Forum Discussion

Marcin's avatar
Marcin
Helper V
7 years ago
Solved

Two years cycle

Hi,   I am looking for a solution. We have special budget let say 500 USD that is available for every employee since contract start date, the amount is restored after two years to new 500 USD.    ...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Marcin,

     

    Please check out the demo in the attachment for details. I have added more data to test with. The formula is like below.

    BudgetLeft =
VAR currentUser = [User]
VAR intervalTimes =
    INT ( DATEDIFF ( [StartDate], TODAY (), DAY ) / 365 / 2 )
VAR nearestDay =
    DATEADD ( 'Calendar'[Date], 2 * intervalTimes, YEAR )
RETURN
    500
        - CALCULATE (
            SUM ( Expense[Amount] ),
            FILTER (
                'Expense',
                'Expense'[Costdate] >= nearestDay
                    && 'Expense'[Costdate] <= TODAY ()
                    && 'Expense'[User] = currentUser
            )
        )

    Two_years_cycle

     

    Best Regards,

    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

Hi MArcin,

 

Can you share a dummy pbix file, please? Would you like the calculated column to show the remaining budget?

 

Best Regards,

Dale