Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Rolling 4 Week Cash Flow

Hello, I am trying to generate a rolling 4 week cash flow. I am unable to send a file because of the private nature of the data so I will try to be succinct as possible.

 

My Weekly Cash Flow measure has the correct value:

Weekly Cash Flow =
[PW AP minus Current Week Cumulative AP]
+[PW Inventory minus Current Week Cumulative Inventory]
+[PW AR minus Current Week Cumulative AR]
+[PW Credit Cards minus Current Week Cumulative Credit Cards]
+[Net Operating Income]
 
My Rolling 4 Week Cash Flow is not correct:
Rolling 4 week Cash Flow =
CALCULATE(
    [Weekly Cash Flow],
    DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-28,DAY))
 
In the example below, the blue rectangle has the incorrect value, that value should be the sum of the values in the pink rectangle.
 

Can someone point out my error? Thank you.

Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi,

    I might be looking at this the wrong way but, would this work?:

    CALCULATE (
    SUM ( [Weekly Cash Flow]),
    DATESINPERIOD ('Date'[Date], MIN ( 'Date'[Date] ), -28, DAY )
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Thank you for your reply. Regrettably the same value is created with this method.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    Since you did not give a specific table, I had to create my own table for testing according to your description, please point out if there are any problems.

    Please try below steps:

    1. below is my test table

    Table:

    2. add a new column with below dax formula

    Index = RANKX('Table',[FW EndOfWeek],,ASC)

    3. create a measure and add it to table visual

    Measure =
VAR cur_index =
    SELECTEDVALUE ( 'Table'[Index] )
VAR tmp =
    FILTER (
        ALL ( 'Table' ),
        'Table'[Index] >= cur_index - 3
            && 'Table'[Index] <= cur_index
    )
VAR sum_for_4weekly =
    SUMX ( tmp, [Weekly Cash Flow] )
RETURN
    IF (
        cur_index < 4,
        SELECTEDVALUE ( 'Table'[Weekly Cash Flow] ),
        sum_for_4weekly
    )

    Please refer the attached .pbix file.

     

    Best regards,
    Community Support Team_ Binbin Yu
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.