Forum Discussion
DAX Question - Interesting
Hi,
Please find attached the PBI file.
Hope this helps.
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
That is exactly what my solution does. See the file and the screenshot.