Forum Discussion
Cumulative Cash Flow By Week
Hello Everyone, I am looking for some help on Cumulative cash flow by week. In my case the starting balance comes from a table which has only one date field which is today and the amount. That amount gets updated every day. Inflow and Outflow are from 2 separate tables. These 3 tables are linked by a date field in calendar table. Can anyone please help me if you have any worked out example. The output should look like the below image.
Hi sudhakar111
here is my solution
https://drive.google.com/drive/folders/1YNAFA2azeKaADsmyPNAbi-8mLoeC6uBg?usp=drive_link
If this helped, please consider giving kudos and mark as a solution
@me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
16 Replies
sudhakar111 , I am assuming you have a Date/week table. This measure can work across measure different tables
Balance =
CALCULATE(firstnonblankvalue('Date'[Month]),sum(Table1[Intial Bal]),filter(all(date),date[date] <min(date[date]))) // Intial Bal +
CALCULATE(SUM(Table[Inflow]),filter(all(date),date[date] <min(date[date]))) -
CALCULATE(SUM(Table[Outflow]),filter(all(date),date[date] <min(date[date])))
Inflow and Outflow can be simaple sum
for BOP /EOP
Onhand BOP= CALCULATE(SUM(Table[Ordered]),filter(all(date),date[date] <min(date[date]))) -
CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <min(date[date])))
onhand EOP= CALCULATE(SUM(Table[Ordered]),filter(all(date),date[date] <=Max(date[date]))) -
CALCULATE(SUM(Table[Sold]),filter(all(date),date[date] <= Max(date[date])))
Again you can add intial Bal if needed in BOP and EOP (Beginning of Period , End of Period)Same as -Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw
Thank you Amit, Based on your input i created a measure and added to the week visual. But the numbers are not adding up. Cash+Inflow-Outflow.
Inventory / OnHand =CALCULATE(Cash[Cash_Position],filter(all('Calendar'),'Calendar'[Date] <min('Calendar'[Date]))) +CALCULATE(SUM(Cash_Inflow[Paid_Estimate]),filter(all(Calendar),Calendar[Date] <min('Calendar'[Date]))) -CALCULATE(SUM(OUTFLOW[PSWBT]),filter(all(Calendar),Calendar[Date] <min('Calendar'[Date])))The Inventory / Onhand on Week 50 Should be the Cash_Position for Week 51.
The Inflow and Outflow can be calculated as
Inflow =
SUM ( InflowTable[Amount] ),
Outflow =
SUM ( OutflowTable[Amount] ),
The initial balance can be calculate like this
Opening Balance =
VAR StartingBalance = SELECTEDVALUE ( Table1[Initial Balance] )
VAR CurrentWeek = SELECTEDVALUE ( 'Calendar'[week] )VAR PrevInflow = CALCULATE ( [Inflow], 'Calendar'[week] < CurrentWeek, REMOVEFILTERS ( 'Calendar' ) )
VAR PrevOutflow = CALCULATE ( [Outflow], 'Calendar'[week] < CurrentWeek, REMOVEFILTERS ( 'Calendar' ) )
RETURN
StartingBalance + PrevInflow + PrevOutflow
The sign in "StartingBalance + PrevInflow + PrevOutflow" might change depending on the sign of the outflow nrs and might need to be StartingBalance + PrevInflow - PrevOutflow
Hope this helps
FB
If this helped, please consider giving kudos and mark as a solution
me in replies or I'll lose your thread
Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page
Consider voting this Power BI idea
Francesco Bergamaschi
MBA, M.Eng, M.Econ, Professor of BI
Thank you Francesco, Tried the formula you mentioned, Please find the output below. the numbesr are not adding up.
Can you please share the pbix via private message?
Hi,
Share the download link of the PBI file. In that file, ensure that there is a Calendar table with a week number column.
Hello Ashish, Please findthe link below. https://drive.google.com/file/d/1DLR3exKOTM-TadVGAs18thLNoYQe7nYG/view?usp=drive_link
sudhakar111 , Please find the attached file after signature. Check my two new measures in the Matrix
