CODEWITHSUNDEEP
powerbi
user17295743
user17295743

Reputation: 59

Create a new table based on value of another table?

I have data relating to Employee name, start and end date, number of shares granted per each year, and number of years the scheme is going to go for.

For example, Employee A has been granted 5000 shares for 4 years on 1st April 2020 and it has vesting period on 1st april 2021,1st april 2022 and 1st april 2023 and 1st april 2024

The data given by the client will have only the Employee name, start and end date (1st April 2020 and 1st April 2024 , number of shares granted per each year (5000) and number of years the scheme is going to vest (4years). It will look like this

enter image description here

Is it possible to create a new table like this in power bi based on the above table enter image description here

I have been able to use this formula in power bi

let StDt = [#"Grant date #(lf)(dd/mm/yyyy)"], AllDates = {Number.From([#"Grant date #(lf)(dd/mm/yyyy)"])..Number.From([#"Vesting end date#(lf)(dd/mm/yyyy)"])},

StofMonthDates = List.Distinct(List.Select(List.InsertRange(List.Transform(AllDates, each Date.StartOfYear(Date.From())),0,{[#"Grant date #(lf)(dd/mm/yyyy)"]}),each Number.From () >= Number.From(StDt))) in StofMonthDates

But since im using the start of the year fucntion it is giving me January 1st and i want it to give the same date as per grant date and only the year should change.

Upvotes: 1

Views: 179

Answers (1)

Marco_CH
Marco_CH

Reputation: 3294

Update: You could create a new table with following code:

Year_Table = 
    GENERATE(
        YourTable,
         FILTER(
             SELECTCOLUMNS(
                 GENERATESERIES(1, 100),
                    "ID", [Value],
                    "End date", DATE(YEAR(YourTable[Grand date]) + [Value], MONTH(YourTable[Grand date]), DAY(YourTable[Grand date]))
                 ),
            [ID] <= YourTable[Vesting period]
            )
        )

Output:

enter image description here

Update, now for each month:

Month_Table = 
    GENERATE(
        YourTable,
         FILTER(
             SELECTCOLUMNS(
                 GENERATESERIES(1, 100),
                    "ID", [Value] / 12,
                    "End date", DATE(YEAR(YourTable[Grand date]), MONTH(YourTable[Grand date]) + [Value], DAY(YourTable[Grand date]))
                 ),
            [ID] <= YourTable[Vesting period]
            )
        )

Upvotes: 2

Related Questions