Forum Discussion
Distribute average between the duration
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:
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
4 Replies
Anonymous , refer if the attached file can help
or the blog
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
