DAX Question - Interesting

[PavB]

Hello All,

I have an 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 produced each year? 

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

I will greatly appreciate your input.

Thanks,

Pav

[parry2k · Accepted solution]

@PavBidani solution attached, tweak it as you see fit.

[parry2k]

@123abc @Good detailed answer but it will not work because total volume will not be distributed based on the agreement years but I liked how you composed your reply. 

[123abc]

To create a bar chart showing fuel produced each year, you can follow these steps using DAX in Power BI or a similar tool:

1. Data Model Setup: First, ensure that your data is loaded into your data modeling tool (like Power BI) with the necessary columns: DATE, FUEL PRODUCER, FUEL PURCHASER, TOTAL VOLUME, and DURATION OF AGREEMENT.

2. Create a Calendar Table: If you don't already have a calendar table, create one. This table will help you create a relationship between dates and the contracts. It's essential for time-based calculations.

3. Define Relationships: Ensure that there's a relationship between the DATE column in your main data table and the date column in the calendar table.

4. Create a Measure: You'll need to create a DAX measure that calculates the total volume produced each year. Here's an example of how to do that:

Total Volume Per Year =
SUM('YourTableName'[TOTAL VOLUME])

1. Replace 'YourTableName' with the actual name of your data table.

2. Create a Bar Chart: Now, create a bar chart visualization. Place the DATE column from your calendar table on the X-axis and the 'Total Volume Per Year' measure on the Y-axis.

3. Filtering: You can add slicers or filters for FUEL PRODUCER and FUEL PURCHASER to allow users to filter the data based on these criteria if needed.

Your bar chart should now display the fuel produced each year based on your data. The 'Total Volume Per Year' measure calculates the sum of total volume for each year, taking into account any filtering or grouping by FUEL PRODUCER and FUEL PURCHASER.

This setup assumes that you have a calendar table and a proper data model. If you don't have a calendar table, you can create one manually or use DAX functions like CALENDAR to generate one. The key to successful time-based calculations in DAX is having a well-defined date/calendar table and establishing relationships between it and your data tables.

[parry2k]

@PavB it's already in the attached pbix file in my previous reply, not sure what have tested at your end. You keep on repeating the same reply without telling if you looked at the already provided solution. 

[parry2k · Accepted solution]

@PavBidani solution attached, tweak it as you see fit.

[PavBidani]

Excellent!!

Thank You!

Pav

[PavBidani]

Hi Parry2k,

The bar chart you are showing in the Fuel.pbix is correct.  

And yes, the total fuel volume is divided equally for all years in the duration of the contract. 

My question is, given the input data below, 2 recrods, how do we create 4 records (which is the right thing to do) to create the bar chart.  

Since real data will have many rows, need to do it automatically (dax or power query), not manually.

Thanks again!

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

[parry2k]

If you pay attention to the table which produced the bar chart, it is already producing the rows in the PQ. You need to out some efforts to look and explore the file before asking these questions. Thanks!

[parry2k]

@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 

[PavBidani]

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

[PavBidani]

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

[Ashish_Mathur · Accepted solution]

Hi,

Please find attached the PBI file.

Hope this helps.

[PavBidani]

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

[Ashish_Mathur]

That is exactly what my solution does.  See the file and the screenshot.

[PavBidani]

Excellent!!

Thank You!

Pav

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark it as Answer.

[PavBidani]

Any time a duration is > 1 year, we need to create additional records in our data set to show yearly volume.  

So when we have 2015, 36 million volume, 3 years duration we create 3 records,

2015 , 12, 3

2016, 12, 3

2017, 12, 3

Regards,

Pav