Forum Discussion
Assign Sprints to Sales Data
Hello Community,
In my Data Model Fact Table I have Sales Data which is always dated for the end of a Month. E.G January 31st 2023 within a Date Column.
The Facttable is related many to One to my Datetable.
I have a Sprint Table which is currenty not related to the FactTable.
In the Sprint Table are Sprint Numberts with a Start and an Enddate. A month will always consist of 2 Sprint.
E.G:
Sprint 1/24 January 1st January to January 15th
Sprint 2/24 January 16th to January 31st
The monthly sales should now be distributed to every Sprint. I need a solution to Dynamically split the Sales Values from the Fact table and assign them to a sprint and create a logical relation between these tables. It would be fine to just split them in half for every Sprint.
First approaches were not successful. I think that I may have to duplicate the Fact Table
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
2 Replies
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
Thanks a lot. This approach worked perfectly!
