Forum Discussion

sudhakar111's avatar
sudhakar111
Helper IV
8 months ago
Solved

Cumulative Cash Flow By Week - Next Year

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
)
Need Help

6 Replies

  • FBergamaschi's avatar
    FBergamaschi
    Super User
    8 months ago

    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

  • amitchandak's avatar
    amitchandak
    Super User
    8 months ago

    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])))

  • v-tejrama's avatar
    v-tejrama
    Community Support
    8 months ago

    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.