Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to Solution.
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]
Thanks . I had missed it
See the attached file here with the solution or attempted solution
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
Hello Zubair,
thanks for your solution it was really helpful,
GENERATE_SERIES was the missing operation I needed.
BR,
Moussab
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]
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.
How is the year portion computed for 2 and 3
Shouldn't this total upto 100 like in case of 1
Looks like it's relative to the quantity and not a percentage
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |