Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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
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
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
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.
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
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.
Hi MArcin,
Can you share a dummy pbix file, please? Would you like the calculated column to show the remaining budget?
Best Regards,
Dale
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |