Manipulating data form.

Frequent Visitor

8 years ago

Solved

Manipulating data form.

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?

Tips and Tricks

Zubair_Muhammad
8 years ago
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

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]

7 Replies

Zubair_Muhammad
Community Champion
8 years ago
moussab_orabi

How is the year portion computed for 2 and 3

Shouldn't this total upto 100 like in case of 1
- jthomson
  Solution Sage
  8 years ago
  Looks like it's relative to the quantity and not a percentage

Community Champion

jthomson

Thanks :smileyvery-happy:. I had missed it

See the attached file here with the solution or attempted solution

Community Champion

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

Community Champion