Forum Discussion
Create Cycle inside tables to duplicate rows.
Hello Everyone,
I am trying to forecast leave for my organization and show the leave balance in the current leave cycle.
I have the following columns
| Person ID | Total Remain | Staff daily cost | Current Month | |
| 1100 | 40 | 60 | 01/06/2018 | |
| 5500 | 20 | 80 | 01/06/2018 | |
| 889 | 10 | 60 | 01/06/2018 |
I have variables which count the current month and the months remaining to the end of the leave cycle.
What I want to obtain is the following, one entry for each month indicating the leave day balance by adding 2.5 days and increasing the month the balance refers to.
| Person ID | Total remain | Staff daily cost | Current Month |
| 1100 | 40 | 60 | 01/06/2018 |
| 1100 | 42.5 | 60 | 01/07/2018 |
| 1100 | 45 | 60 | 01/08/2018 |
| 1100 | 47.5 | 60 | 01/09/2018 |
| 1100 | 50 | 60 | 01/10/2018 |
| 1100 | 52.5 | 60 | 01/11/2018 |
| 1100 | 55 | 60 | 01/12/2018 |
| 1100 | 57.5 | 60 | 01/01/2019 |
| 1100 | 60 | 60 | 01/02/2019 |
| 1100 | 62.5 | 60 | 01/03/2019 |
| 5500 | 20 | 80 | 01/06/2018 |
| 5500 | 22.5 | 80 | 01/07/2018 |
| 5500 | 25 | 80 | 01/08/2018 |
| 5500 | 27.5 | 80 | 01/09/2018 |
| 5500 | 30 | 80 | 01/10/2018 |
| 5500 | 32.5 | 80 | 01/11/2018 |
| 5500 | 35 | 80 | 01/12/2018 |
| 5500 | 37.5 | 80 | 01/01/2019 |
| 5500 | 40 | 80 | 01/02/2019 |
| 5500 | 42.5 | 80 | 01/03/2019 |
What I tried at the moment is:
Absance balanca details = GENERATE(Absence_balance,SELECTCOLUMNS(GENERATESERIES(MONTH(TODAY()),[Months to end cycle]+1),"Remain",Absence_balance[Total remain.]+2.5,"Month",[Month Counter]+1))
This is able to duplicate all the rows, the number of times I need. Of course the values [total reamin] and [month counter] are not changing but staying the same, I left the +2.5 and +1 to show you what I am trying to obtain. What can I do to have the system go through the cycle and increase the values I need to be increased?
Thanks a lot,
Mike
Hi Mike22,
>>As you can see it is increasing 2.5 every month but it is giving me the full list of all days of the month. Is it possible to have one value for every month only?
I think it is easy to achieve, you only need to add filter function to choose which day you want to keep.
Expand = VAR _start = FIRSTDATE ( 'Sample'[Current Month] ) VAR _calendar = CALENDAR ( _start, DATE ( YEAR ( _start ), 3, 30 ) ) RETURN FILTER ( SELECTCOLUMNS ( FILTER ( ADDCOLUMNS ( CROSSJOIN ( 'Sample', _calendar ), "Rolling", [Total Remain] + 2.5 * DATEDIFF ( [Current Month], [Date], MONTH ) ), [Rolling] <= [Staff daily cost] + 2.5 ), "Person ID", [Person ID], "Rolling", [Rolling], "Staff daily cost", [Staff daily cost], "Date", [Date] ), DAY ( [Date] ) = 10 )You can also modify 'date' column to year month, then use distinct function to filter duplicate records.
Regards,
Xiaoxin Sheng
6 Replies
HI Mike22,
Please try to use below table formula if it suitable for your requirement.
Expand = VAR _start = FIRSTDATE ( 'Sample'[Current Month] ) VAR _calendar = CALENDAR ( _start, DATE ( YEAR ( _start ), MONTH ( _start ) + 1, 1 ) - 1 ) RETURN SELECTCOLUMNS ( FILTER ( ADDCOLUMNS ( CROSSJOIN ( 'Sample', _calendar ), "Rolling", [Total Remain] + 2.5 * DATEDIFF ( [Current Month], [Date], DAY ) ), [Rolling] <= [Staff daily cost] + 2.5 ), "Person ID", [Person ID], "Rolling", [Rolling], "Staff daily cost", [Staff daily cost], "Date", [Date] )Notice: I haven't test with sample data who contains multiple months.
Regards,
Xiaoxin Sheng
Anonymous
Thank you very much this is great and works perfectly. Just two things please.
-I have a variable which indicates the month in which the cycle ends, where do I add this to the formula? So the rows should stop duplicating at the month of march.
-There was probably a mis interpretation on the date time format. I want to add 2.5 on a monthly basis not on a daily basis as it is now.
Best regards,
Mike
HI Mike22,
#1. You can modify calendar variable to manually set end date.
#2. You can simply modify datediff function to configure date unit.Expand = VAR _start = FIRSTDATE ( 'Sample'[Current Month] ) VAR _calendar = CALENDAR ( _start, DATE ( YEAR ( _start ), 3, 30 ) ) RETURN SELECTCOLUMNS ( FILTER ( ADDCOLUMNS ( CROSSJOIN ( 'Sample', _calendar ), "Rolling", [Total Remain] + 2.5 * DATEDIFF ( [Current Month], [Date], MONTH ) ), [Rolling] <= [Staff daily cost] + 2.5 ), "Person ID", [Person ID], "Rolling", [Rolling], "Staff daily cost", [Staff daily cost], "Date", [Date] )Regards,
Xiaoxin Sheng
