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

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's avatar
    v-jiascu-msft
    Microsoft Employee
    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