Forum Discussion

tcasekpi's avatar
tcasekpi
New Member
6 years ago
Solved

Rolling Balance Sheet Balance

Hello,    I am new to Power BI and I am having trouble adding a cumulative column.    I have a table that shows the actitvity by account by month that I want to use to show the ending balance eve...
Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    Hi tcasekpi ,

     

    You could create a date table as a date filter. Then refer to the following measure:

    Measure =
VAR a =
    CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
VAR b =
    EOMONTH ( a, -12 )
RETURN
    CALCULATE (
        SUM ( 'Table'[Amount] ),
        FILTER (
            ALLEXCEPT ( 'Table', 'Table'[Account] ),
            'Table'[End of Month] <= MAX ( 'Table'[End of Month] )
                && SELECTEDVALUE ( 'Table'[End of Month] ) <= a
                && SELECTEDVALUE ( 'Table'[End of Month] ) >= b
        )
    )

    Here is my test file for your reference.

     

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

tcasekpi ,

Please refer to these example with Date table

Monthly cummlative

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

Qtr

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))

 

Year

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

 

overall

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=max(Sales[Sales Date])))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

 

  • tcasekpi's avatar
    tcasekpi
    New Member
    6 years ago

    Thank you for the reply. This causes a circular dependency in my table. I do not know how to resolve. 

    • andre's avatar
      andre
      Icon for Memorable Member rankMemorable Member
      6 years ago

      are you creating it as a column or a measure?  you should be doing it as a measure, also make sure you have a valid date table in your model

      • tcasekpi's avatar
        tcasekpi
        New Member
        6 years ago

        I created it as a measure and use a dates table. What I am ultimately trying to do is take a transaction table and convert it to show 12 month rolling balance sheet balances based on a date slicer. 

         

        I see a number of examples that work for revenue accounts but none for balance sheet cumulative totals.