Forum Discussion
DAX Question - Interesting
Hi,
Please find attached the PBI file.
Hope this helps.
PavB what is the expected output? Is that total volume equally distributed to all the agreement years? Provide details that help to answer the question, without the business logic, not sure how one can assist. Read this post to get your answer quickly.
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Hi Parry,
Expected output is below.
| Year | FUEL PRODUCER | FUEL PURCHASER | TOTAL VOLUME ( MILLIONS OF LITERS) | DURATION OF AGREEMENT (YEARS) |
| 2014 | ABC | United Airlines | 10 | 1 |
| 2015 | PQR | Delta | 12 | 3 |
| 2016 | PQR | Delta | 12 | 3 |
| 2017 | PQR | Delta | 12 | 3
|
We divide the total fuel volume with the duration of the contract, and add records for each successive year for that producer and purchaser for the avg fuel per year.
I hope this helps.
Thanks,
Pav
i hope this explains it a little better -
Interesting question –
In my data, I have fuel producer, fuel purchaser, contract start date, contract duration, and total volume for the contract duration.
How do I show a bar chart with fuel purchased each year?
If Delta is signing an agreement in 2015, to purchase 36 Million liters over 3 years, then that should reflect as 12 million in 2015, 12 million in 2016, and 12 million in 2017.
AGREEMENT DATE
FUEL PRODUCER
FUEL PURCHASER
TOTAL VOLUME ( MILLIONS OF LITERS)
DURATION OF AGREEMENT (YEARS)
Sep 24, 2014
ABC
United Airlines
10
1
Aug 7, 2015
PQR
Delta
36
3
Expected output for data manipulation is below. Below data will be used to create a bar chart.
Year
FUEL PRODUCER
FUEL PURCHASER
TOTAL VOLUME ( MILLIONS OF LITERS)
DURATION OF AGREEMENT (YEARS)
2014
ABC
United Airlines
10
1
2015
PQR
Delta
12
3
2016
PQR
Delta
12
3
2017
PQR
Delta
12
3
We divide the total fuel volume with the duration of the contract, and add records for each successive year for that producer and purchaser for the avg fuel per year.
I hope this helps.
Is it better to do this in SQL?
Thanks,
Pav
Hi,
Please find attached the PBI file.
Hope this helps.
Thanks Ashish.
The input data is shown below - 2 rows.
How do we split a 3 year duration contract into 3 years 2015, 2016, 2017 with a volume of 12 (36/3) each year?
Once we have that, then we can create a bar chart that shows years 2014 thru 2017.
Thanks,
Pav
YEAR FUEL PRODUCER FUEL PURCHASER TOTAL VOLUME ( MILLIONS OF LITERS) DURATION OF AGREEMENT (YEARS) 2014 ABC United Airlines 10 1 2015 PQR Delta 36 3