Rolling 4 Week Cash Flow

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

Hello, I cleaned up my file and copied it here: https://drive.google.com/drive/folders/1k0Nw-TT7gOr0tOW6Nn9AmDKyoOPPTbNJ?usp=sharing Any additional help you can provide is appreciated.

[Anonymous]

Thank you for your help. My Weekly Cash Flow is in a different related table from my date table so I am getting this error:

[Anonymous]

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]

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