Cumulative Cash Flow By Week - Next Year

[sudhakar111]

Hello All, I was creating a report for cumulative cash flow by week using the below formula. It works fine but only for this year. Week 52 cash balance has to be carry forwarded to next year week 1.

Opening Cash  =

VAR StartingBalance = CALCULATE(SELECTEDVALUE('Cash'[Opening_Cash]), REMOVEFILTERS( 'Calendar' ) )

VAR StartingDate = CALCULATE( SELECTEDVALUE( Cash[Date] ), REMOVEFILTERS( 'Calendar' ) )

VAR StartingWeek = CALCULATE( SELECTEDVALUE( 'Calendar'[YearWeekNr] ), 'Calendar'[Date] = StartingDate, REMOVEFILTERS( 'Calendar'[Week] ) )

VAR CurrentWeek = SELECTEDVALUE ( 'Calendar'[YearWeekNr] )

VAR TotalInflowPrevWeek = CALCULATE( SUM(Cash_Inflow[In_Flow]), 'Calendar'[YearWeekNr] <= CurrentWeek - 1, 'Calendar'[YearWeekNr] >= StartingWeek, REMOVEFILTERS( 'Calendar' ) )

VAR TotalOutflowPrevWeek = CALCULATE( SUM(OUTFLOW[Out_Flow]), 'Calendar'[YearWeekNr] <= CurrentWeek - 1, 'Calendar'[YearWeekNr] >= StartingWeek,REMOVEFILTERS( 'Calendar' ) )

RETURN //CurrentWeek

IF (

    CurrentWeek = StartingWeek, StartingBalance,

    StartingBalance

   +

   TotalInflowPrevWeek

    -

     TotalOutflowPrevWeek

)

[v-tejrama]

Hi @sudhakar111 ,
Thanks for reaching out to the Microsoft fabric community forum.

The issue appears to stem from using the YearWeekNr field for your cumulative calculation, as it resets each year. This causes the running balance to function within the current year but not carry forward into the next. To address this, I recommend adding a continuous week index to your Date table that does not reset annually.

By creating a Week Rank column using a dense rank over the Date field, you will have an uninterrupted sequence of weeks for your entire model. Your cumulative inflow and outflow measures can then reference this continuous rank, and your opening balance will only need to be retrieved once from your source table. With these adjustments, your cash balance measure will consistently carry forward across multiple years, eliminating the need for complex filtering logic and resolving the issue with the running balance.

Thank you,

Tejaswi.

[sudhakar111]

Thank you

[FBergamaschi · Accepted solution]

Hi @sudhakar111 

I modified my solution on the previous post to accomodate your request

Here the new solution

https://drive.google.com/drive/folders/1YNAFA2azeKaADsmyPNAbi-8mLoeC6uBg?hl=it

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

[sudhakar111]

@FBergamaschi  This worked perfectly. Thank you.

[amitchandak]

@sudhakar111 , Please create Rank on Year Week column of you date table then you can take it across year 

New columns

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format

Cumm Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]<=max('Date'[Week Rank])))

[sudhakar111]

Thank you Amit