End of month running total

[Anonymous]

Hi there,

I have two table Date with Datekey and CashFlow with CashFlow Amount and DateKey

One think to remember is my CashFlow date is the end of month date and I would like to calculate running total for my Cashflow

My date table start from 2015 to 2020 so not all projects start from 2015 some start 2016 and end 2018 some start 2015 and in 2019 

and some start 2019 and still continue

I created a measure 

RT = CALCULATE(

Sum(CashFlow[CashFlow]),

FILTER(all('Date'[Datekey]),'Date'[Datekey] <= Max('Date'[Datekey])))

But from some reason it show same as cashflow column and not running total as expected (see picture)
Why is that? because is end of month only? (I'v tried also with Date[Date] column but same results

Thanks,

Oded Dror
Running Total

[FrankAT]

Hi @Anonymous 

is this what you are looking for?

RT = 
CALCULATE(
    [Sum of CashFlow],
    FILTER(
        ALL(CashFlow),
        CashFlow[Date] <= MAX(CashFlow[Date])
    )
)

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[Anonymous]

Paul,

You have a different file, is not working the last row of Rt need to the the sum of the cash flow amount and when I'm using the job slicer the amount dosen't change? the date table is not taking in concideration from some reson.

Thanks,

Oded Dror

[PaulDBrown]

@Anonymous 

Can you provide a sampe dataset or sample PBIX file?

(BTW, this may be a silly question but are all date fields set as a date?)

[Anonymous]

Paul,

Here is the file
https://1drv.ms/u/s!Av1ewa2NOwEThM5C3t53swV3U-x1cQ?e=8YQLhs 

Thanks,

Oded Dror

[PaulDBrown · Accepted solution]

@Anonymous 

As per @FrankAT  solution, the problem is with the Running Total calculation (in particular the SUM('Cashflow'[Cashflow]).

To solve it just create a first measure for:

 Sum of Cashflow = SUM(CashFlow[CashFlow])​

And the use this measure in the running total measure:

Cashflow Running Total =
CALCULATE([Sum of Cashflow],
FILTER(ALL('Date Table'),
'Date Table'[Datekey] <= MAX('Date Table'[Datekey])))

Btw it doesn't work if you try to use a VAR for the Sum of Cashflow either (even if you wrap it in a CALCULATE function). They need to be separate measures.

[Anonymous]

Paul,

I added additional job filter and it works

RT = CALCULATE(

[Sum of CashFlow],

FILTER(

All('CashFlow'),

CashFlow[Date] <= max(CashFlow[Date]) && CashFlow[Job] = SELECTEDVALUE(CashFlow[Job] ))

)

Thaks,

Oded Dror

[Anonymous]

Paul,

How do I insert PBIX? 

Thanks,

Oded Dror

[PaulDBrown]

@Anonymous 

The best way is to upload to a cloud service (Onedrive, Dropbox, Google Drive, iCloud...) and share from there

[Anonymous]

Paul,

Thank for quick response and yes date columns are dates and date table marked as date table with date column.
I can't provide you this one but let me create same value and dataset and I will contact you again give me few minutes

Thanks

Oded Dror