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

Marcin's avatar
Marcin
Helper V
7 years ago

That works for me, thanks.

 

I have one question, why do we need Time table column Date for Dateadd function instead of using Startdate from Users ?

 

I can't create relationship between Users and my Time table, so had to create another Time table only for this solution. 

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

Hi Marcin,

 

Because DATEADD is a time intelligence function which needs a full and continuous date column as its first parameter. Was there an error message that you can't create a relationship? 

 

Best Regards,

Dale

  • Marcin's avatar
    Marcin
    Helper V
    7 years ago

    Hi,

     

    the error was that multiple value was entered and one value was expected. Between Users and Time tables I have table with Events where working hours are reported, so that we could check events by User and by Time.