Forum Discussion

Mike22's avatar
Mike22
Helper III
8 years ago
Solved

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 cos...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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's avatar
Mike22
Helper III
8 years ago

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

Anonymous's avatar
Anonymous
Not applicable
8 years ago

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's avatar
    Mike22
    Helper III
    8 years ago

    Anonymous

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        Mike22
        Helper III
        8 years ago

        Anonymous

         

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