Manipulating data form.

[moussab_orabi]

Hello every one, I need help with manipulating this data:
odred    quantity     valid_form      valid_to 

1             100            01.01.2016     31.05.2017

2              50             01.02.2016     30.06.2016

3              250            01.03.2015    31.01.2017

I want to accumulate the quantity per year for each order, so the data will be like following.

odred    quantity     valid_form    valid_to          year         year_portion 

1             100            01.01.2016     31.05.2017       2016        70.59

1             100            01.01.2016     31.05.2017       2017        29.41

2              50             01.02.2016     30.06.2016       2016        50

3              250            01.03.2015    31.01.2017       2015        108.7

3              250            01.03.2015    31.01.2017       2016        130.43

3              250            01.03.2015    31.01.2017       2017        10.87


any ideas to do that please?

[Zubair_Muhammad · Accepted solution]

@moussab_orabi

Following are the steps

Step#1 Create a New Table

Go to Modelling Tab >>>NEW TABLE

New_Table =
GENERATE (
    TableName,
    GENERATESERIES (
        YEAR ( TableName[Valid From] ),
        YEAR ( TableName[Valid To] ),
        1
    )
)

Step# 2 Change the Column Name from "Value" to YEAR

[Zubair_Muhammad]

@jthomson

Thanks . I had missed it

@moussab_orabi

See the attached file here with the solution or attempted solution

[Zubair_Muhammad · Accepted solution]

@moussab_orabi

Following are the steps

Step#1 Create a New Table

Go to Modelling Tab >>>NEW TABLE

New_Table =
GENERATE (
    TableName,
    GENERATESERIES (
        YEAR ( TableName[Valid From] ),
        YEAR ( TableName[Valid To] ),
        1
    )
)

Step# 2 Change the Column Name from "Value" to YEAR

[moussab_orabi]

Hello Zubair,
thanks for your solution it was really helpful,
GENERATE_SERIES was the missing operation I needed.

BR,
Moussab

[Zubair_Muhammad · Accepted solution]

@moussab_orabi

Step # 3 Add the YEAR PORTION column to this NEW TABLE

Year_Portion =
IF (
    YEAR ( 'New_Table'[Valid From] ) = 'New_Table'[YEAR],
    DIVIDE (
        DATEDIFF (
            'New_Table'[Valid From],
            MIN ( DATE ( YEAR ( 'New_Table'[Valid From] ), 12, 31 ), 'New_Table'[Valid To] ),
            DAY
        ),
        DATEDIFF ( 'New_Table'[Valid From], 'New_Table'[Valid To], DAY )
    ),
    IF (
        'New_Table'[YEAR] > YEAR ( 'New_Table'[Valid From] )
            && 'New_Table'[YEAR] < YEAR ( 'New_Table'[Valid To] ),
        365 / DATEDIFF ( 'New_Table'[Valid From], 'New_Table'[Valid To], DAY ),
        DIVIDE (
            DATEDIFF (
                DATE ( YEAR ( 'New_Table'[Valid to] ) - 1, 12, 31 ),
                'New_Table'[Valid To],
                DAY
            ),
            DATEDIFF ( 'New_Table'[Valid From], 'New_Table'[Valid To], DAY )
        )
    )
)
    * 'New_Table'[Quantity]

[Zubair_Muhammad]

@moussab_orabi

Final output looks like this

It would be in accurate by decimals. I think because DateDiff formula misses one Day i..e to say Datediff between 1 Jan and 31 Dec is 364 days.

[Zubair_Muhammad]

@moussab_orabi

How is the year portion computed for 2 and 3

Shouldn't this total upto 100 like in case of 1

[jthomson]

Looks like it's relative to the quantity and not a percentage