How to Parse values into Monthly bins when I only have a Start & End Date

[Roseventura]

I have a data file that is made up of Awards that Customers give to us.  Each award is made up of Customer, part number, start date of award, end date of award, qty, price, etc.  Our sales team needs to sell the parts within the award period.  We then need to compare what they sold against what was awarded.

Here are the characteristics of Awards:

1. The award file can contain many awards from many customers.
2. There may be more than 1 award from a customer, but the award start/end dates would never overlap.  For example, you may have an award for Customer “A” that goes from 1/1/2019 – 6/30/2019, but then the next award would always start AFTER that (7/1/2019).
3. Customer awards can be either quarterly, bi-annually, or annually.  Each customer has their own award timeframe and most customers stick to that.  In other words, if awards for Customer “A” are quarterly, then they usually stick to that.  They’d never have 1 award that was quarterly, then another that was annual.
4. Customer awards could span from one year to the next (i.e., 6/30/2019 – 6/30/2020).

Here’s what I’ve done so far:

• Using the Start / End date, I can figure out the length of the award (3 months, 6 months, 12 months).
• Using the # of months I am calculating the Qty/award length and the Ext price/award length to determine how many parts they need to sell per month (example:  if the award Qty is 15,000, then over 6 months the sales team needs to sell 2,500 per month.)

What I need help with is this:

• Because I only have a Start Date and End Date for the award, I need to parse out the months over the length of each award so I can show the Award Qty and Price by month.  This will then enable me to compare the Award Qty and Price against the Sales Qty and Price.

[Roseventura]

Hi Kelly,

Your suggestion comes close, but it doesn't address the issue of when a customer has MORE THAN ONE award in a year.  For instance, Customers can award us different quantities to sell over different time periods.  They can even NOT award us anything for a time period.  For example, the top table shows how several customers break down their awards.  The second table shows how I need to break down each award over the Min/Max time period:

Your suggestion ignores any awards prior to the latest award.

Rose

[v-kelly-msft · Accepted solution]

Hi @Roseventura ,

The method is similar.

First you need to convert your table to below format:(No merged cells inside)

Then go to query editor,select columns"Q1","Q2","Q3","Q4">choose"unpivot columns":

Then back to data view,create a calerdar table as suggested above,then create a new column as below:

Quarter = "Q"&QUARTER('Calerdar'[Date])

And a measure as below:

Measure 2 = 
var _value=CALCULATE(MAX('Table (2)'[Value]),FILTER('Table (2)','Table (2)'[Attribute]=MAX('Calerdar'[Quarter])&&'Table (2)'[categoty]=MAX('Table (2)'[categoty])))
Return
IF(MAX('Calerdar'[Quarter]) in FILTERS('Table (2)'[Attribute]),_value/3,BLANK())

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Roseventura]

The above solution did not work for me because it doesn't address when a specific customer has more than one award per year (if they give us an award every quarter that's 4 awards per year, so that's 4 start/end dates per year). 

I managed to parse out my data file BEFORE it gets to Power BI so the data file arrives with everything broken down into monthly buckets. 

I'll leave this marked as the solution because it may be helpful to someone else.

[Roseventura]

Here is an example of what I'm trying to achieve in spreadsheet form.  The BLUE area is a sample row of data.  The GREEN are measures I currently have.  The ORANGE area is what I need help with:

I need to take what you have, but distribute it between months, not days.  I know it's similar, but I'm having trouble applying my issue in the PBIX you sent me.

I don't know how to upload the PBIX to this message area.

Rose

[v-kelly-msft]

Hi @Roseventura ,

First create a dimention table as below:

dim table = CALENDAR(MIN('Table'[Start date ]),MAX('Table'[end date]))

Then create a column as below:

Year-month = YEAR('dim table'[Date])&"-"&MONTH('dim table'[Date])

And a measure as below:

Measure = IF(MAX('Table'[Start date ])<=MAX('dim table'[Date])&&MAX('Table'[end date])>=MAX('dim table'[Date]),MAX('Table'[award qty by month]),BLANK())

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@Roseventura ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Two way you can explore. One is file attached after signature or https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...