Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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])
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.
Proud to be a Super User!
Paul on Linkedin.
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)
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
@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?)
Proud to be a Super User!
Paul on Linkedin.
@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])
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.
Proud to be a Super User!
Paul on Linkedin.
Paul,
I added additional job filter and it works
Paul,
How do I insert PBIX?
Thanks,
Oded Dror
@Anonymous
The best way is to upload to a cloud service (Onedrive, Dropbox, Google Drive, iCloud...) and share from there
Proud to be a Super User!
Paul on Linkedin.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |