Duplicate the max value (forecasting)

[Anonymous]

Hi,

I have a simple table with three columns (10000 rows):

Employee	Month	Team
001021	1-8-2020	A10
001021	1-9-2020	A10
001981	1-8-2020	B12
005251	1-8-2020	D01
005251	1-9-2020	B18

This information is only available for the months that have passed. For forecasting purposes I would like to copy the last months data into a new month.

The solution I have so far:
- Pivot the data with month as the new column headers and team as the value;

- copy the far right column (the last known month), make it next month;

- Unpivot and done.

This requires manual work though each month, so I would like to know if there is a way to do this automatically.

(The data is sorted primarily on employee already, with month as second sorting order).

Thanks in advance

[v-xicai]

Hi @Anonymous ,

You may create calculated column or measure like DAX below.

Column1= CALCULATE (MAX( Table1[Month] ),FILTER ( Table1, Table1[Employee]= MAX(Table1[Employee])))



Measure1= CALCULATE (MAX( Table1[Month] ),FILTER ( Table1, Table1[Employee]= EARLIER(Table1[Employee])))

Best Regards,

Amy 

Community Support Team _ Amy

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

[Anonymous · Accepted solution]

Maybe by references the query in a new one,

filtering out the max month, adding a month and then append that query? 😅