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. 

 

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 

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

6 Replies

  • 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

  • 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.