Create Cycle inside tables to duplicate rows.

[Mike22]

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

[v-shex-msft]

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

[Mike22]

@v-shex-msft

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

[v-shex-msft]

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

[Mike22]

@v-shex-msft

It is working thanks, only one small issue left, I guess.

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 have reformatted date to be only year and month so if there is a way to remove duplicates by ID and month it would result in one entry per month.

Let me know if you have any suggestions and thanks a lot for the great help.

Mike

[v-shex-msft · Accepted solution]

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

[Mike22]

@v-shex-msft

This worked perfectly. Thank you so much for your help.