Future Allocations based on Start Date and End Date

[dawgpile]

New(ish) to Power BI. This is for physical commodity trades. I want to extract commodity allocation base on date. 

I receive a Excel trade report for a commodity (let's say sugar) that has all trades listed by Start Date, End Date and Volume and Counterparty of trade. The line items I receive in excel look like this

Sell 10 tons/day to XYZCompany Starting 7/1/2019 thru 5/1/2020

I want to see how much sugar that I have allocated for future months, quarters year. 

Suggestions for a formula? Please lemme know if I need to explain further.

[lc_finance · Accepted solution]

HI @dawgpile ,

You can create a Date table, then add a measure to this table to count the sugar to sell on each day.

I wrote a post on a similar on using this approach for counting employees:

https://finance-bi.com/power-bi-employee-count-by-month/ 

While the topic is different, the Power BI solution can be very similar.

Does this help you?

If it doesn't, can you share a sample Power BI file? You can share it using OneDrive, Google Drive or a similar tool.

LC

Interested in Power BI and DAX templates? Check out my blog at www.finance-bi.com

[dawgpile]

This an excellent start. Thank you very much! I walked through your tutorial and was able to generate a count.

The next step is to get a sum of all the tons I have allocated in future months. Similar to a SUMIF maybe

Off to search the forums! Appreciate your help.

[lc_finance]

HI @dawgpile ,

I am glad you were able to progress using the tutorial.

SUMIF does not exist in Power BI however you can reproduce it using SUMX and IF.

Here is an example:

SUMIF measure =
SUMX (table, IF(condition, number to be added, 0))

If you wish, you can share more about what you want to build and I'd. be happy to help you further

LC

[v-lili6-msft]

hi @dawgpile 

For your case, you could also try this 

https://community.powerbi.com/t5/Desktop/Calendar/m-p/689645#M332529

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

If you still have the problem, please share some simple sample data and your expected output.

Regards,

Lin