Two years cycle

[Marcin]

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. 

I wolud like to create a calculated column ( CurrentBudget)  for every user where Current budget will be calculated according to two years cycle. 

I have another table where all expences ( Amount, UserID, Date)  are reported and should be deducted from total amount of budget in each cycle of two years. 

Any advice ?

MArcin 

[Marcin]

I wolud like to add calculated column in Users table to show current budget amount according to costs and users start date. 

I don't know how to share pbix file here so I put some data, 

User     Username   StartDate
User1   Adam          01.05.2018
User2   Paul             01.07.2018
User3   Jim              01.09.2018

User    Costdate          Amount   Description
User1   21.08.2018      300          Mouse
User1   22.08.2018      35            Book
User2   02.08.2018      150          Bag
User3   03.09.2018      250         Headphones

[v-jiascu-msft · Accepted solution]

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

Best Regards,

Dale

[Marcin]

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]

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]

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. 

[v-jiascu-msft]

Hi MArcin,

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

Best Regards,

Dale