Assign Sprints to Sales Data

Hi BIlix 

No need to duplicate the Fact table. But some modelling is required.

Open Power Query

In the Fact Table do the following

• Highlight the date column
• Go to the ribbon and the Add Column tab
• Click on the Date and Choose Year this will create a Year Column
• Highlight the date column
• Click on date and Choose Month then Month. this create a numerical Month column

In the Sprint table

• Highlight the StartDate Column and repeat the above steps with adding a Year and Month column

In the Fact table

• Click in the Ribbon under the Home tab the MergeQueries option
• In the second filed choose the Sprint table
• Highlight the Year column and hold shift then highlight the month column in both tables in the same order

  . this is important for merging reasons.

  

   

• Press ok
• Expand the new column and choose only the Sprint name. 
• This will create duplicate values for each Sales row.
• Now, as we know there are only two Sprints per month, highlight the SalesAmount column
• Go to the ribbon and the transform tab. 
• Click on the Standard option then Divide and enter 2 in the value field. this will divide the value by 2

Load the data into the report. 

Open a table visual add the Sprint column from the Sales table and then the Sales value column to get the result you need

Thanks

Joe

If this post helps, then please Accept it as the solution