Distribute average between the duration

[Anonymous]

Hi! I have a sales with the start date, end date and duration. In order to get the sales per month, I divided the sales from the duration. Now I want to show the sales in each month instead of the start month. See below:

As an example, I want to show 333.33 for 12 consecutive months

My original table shows as:

[Anonymous · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Enter Power query, copy Table to form Table2.

2. Click Add column – Customer column and enter the following code.

{Number.From(List.Min(#"Changed Type"[#"STARTDATE"]))..Number.From(List.Max(#"Changed Type"[#"ENDDATE"]))}

3. Click on the new row – Expand to New Rows.

4. Right click on [Date] - Change Type - Date/Time.

5. Click Add Column – Custom Column – to create two columns.

Customer.1 ：

Date.Year([Date])

Customer.2：

Date.MonthName([Date])

6. Select the new two columns [Customer.1], [Customer.2] – Merge Column.

7. Select unwanted columns – Remove Columns.

8. Copy Table2 to form Table3, and select [Date] for Remove Columns.

Select Add Column – Index Column.

9. Create measure.

Flag =
IF(
    MAX('Table2'[Date])>=MAX('Table'[STARTDATE])&&MAX('Table2'[Date])<=MAX('Table'[ENDDATE]),MAX('Table'[HRS_PER_MONTH]),BLANK())

10. Click on Table3, select [Merged] - Column tools - Sort by column - Index.

11. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Can I please know the data model (joins) of this solution? 

[Anonymous]

@Anonymous any luck on the model please. I do not get the required result

[amitchandak]

@Anonymous , refer if the attached file can help

or the blog

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785