Forum Discussion

Ajungx's avatar
Ajungx
Frequent Visitor
5 years ago
Solved

Invoice Payment Running Balance (Cummulative Total Balance)

I have a table with invoices and a table payments. I want to create a visual table like the one above. Anyone who can help me is very grateful 🙏

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-xuding-msft's avatar
    v-xuding-msft
    5 years ago

    Hi Ajungx ,

     

    Please try this:

     

    • Create a date table

     

    Date = 
VAR t =
    CALENDAR ( MIN ( Invoicing[Date] ), MAX ( Payment[Date] ) )
RETURN
    ADDCOLUMNS (
        t,
        "Year", YEAR ( [Date] ),
        "Month", FORMAT ( [Date], "mmmm" ),
        "YearMonthNo",
            YEAR ( [Date] ) * 100
                + MONTH ( [Date] )
    )

     

    • Create measures

     

    Invoicing = 
VAR year_ =
    SELECTEDVALUE ( 'Date'[Date].[Year] )
VAR customer =
    SELECTEDVALUE ( Invoicing[Customer] )
VAR a =
    CALCULATE (
        SUM ( Invoicing[Amount] ),
        FILTER (
            ALL ( 'Invoicing' ),
            Invoicing[Date].[Month] = MAX ( 'Date'[Date].[Month] )
        )
    )
VAR b =
    CALCULATE (
        SUM ( Invoicing[Amount] ),
        FILTER (
            'Invoicing',
            Invoicing[Date].[Month] = MAX ( 'Date'[Date].[Month] )
                && ( Invoicing[Date].[Year] = year_
                || Invoicing[Customer] = customer )
        )
    )
RETURN
    IF (
        ISFILTERED ( 'Date'[Date].[Year] ) || ISFILTERED ( Invoicing[Customer] ),
        b,
        a
    )

    Payment = 
VAR year_ =
    SELECTEDVALUE ( 'Date'[Date].[Year] )
VAR customer =
    SELECTEDVALUE ( Invoicing[Customer] )
VAR a =
CALCULATE (
    SUM ( Payment[Paid Amt] ),
    FILTER ( ALL ( Payment ), Payment[Date].[Month] = MAX ( 'Date'[Date].[Month] ) )
)

VAR b =
    CALCULATE (
    SUM ( Payment[Paid Amt] ),
    FILTER (  Payment , Payment[Date].[Month] = MAX ( 'Date'[Date].[Month] ) 
                && ( Payment[Date].[Year] = year_
                || Payment[Customer] = customer )
        )
    )
RETURN
    IF (
        ISFILTERED ( 'Date'[Date].[Year] ) || ISFILTERED ( Invoicing[Customer] ),
        b,
        a
    )

     

    Balance = 
VAR LastYearMonthNo =
    MAX ('Date'[YearMonthNo])
VAR MonthToSum =
    FILTER (
        ALLSELECTED (
            'Date'[Month],
            'Date'[YearMonthNo]
        ),
        'Date'[YearMonthNo] <= LastYearMonthNo
    )
RETURN
SUMX(MonthToSum,[Invoicing]-[Payment])

     

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Ajungx , Join both on them with a common date table and try like

     

    Cumm = CALCULATE([Invoicing],filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE([payment],filter(date,date[date] <=maxx(date,date[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


    Appreciate your Kudos.

  • v-xuding-msft's avatar
    v-xuding-msft
    Community Support
    5 years ago

    Hi Ajungx ,

     

    Please try this:

     

    • Create a date table

     

    Date = 
VAR t =
    CALENDAR ( MIN ( Invoicing[Date] ), MAX ( Payment[Date] ) )
RETURN
    ADDCOLUMNS (
        t,
        "Year", YEAR ( [Date] ),
        "Month", FORMAT ( [Date], "mmmm" ),
        "YearMonthNo",
            YEAR ( [Date] ) * 100
                + MONTH ( [Date] )
    )

     

    • Create measures

     

    Invoicing = 
VAR year_ =
    SELECTEDVALUE ( 'Date'[Date].[Year] )
VAR customer =
    SELECTEDVALUE ( Invoicing[Customer] )
VAR a =
    CALCULATE (
        SUM ( Invoicing[Amount] ),
        FILTER (
            ALL ( 'Invoicing' ),
            Invoicing[Date].[Month] = MAX ( 'Date'[Date].[Month] )
        )
    )
VAR b =
    CALCULATE (
        SUM ( Invoicing[Amount] ),
        FILTER (
            'Invoicing',
            Invoicing[Date].[Month] = MAX ( 'Date'[Date].[Month] )
                && ( Invoicing[Date].[Year] = year_
                || Invoicing[Customer] = customer )
        )
    )
RETURN
    IF (
        ISFILTERED ( 'Date'[Date].[Year] ) || ISFILTERED ( Invoicing[Customer] ),
        b,
        a
    )

    Payment = 
VAR year_ =
    SELECTEDVALUE ( 'Date'[Date].[Year] )
VAR customer =
    SELECTEDVALUE ( Invoicing[Customer] )
VAR a =
CALCULATE (
    SUM ( Payment[Paid Amt] ),
    FILTER ( ALL ( Payment ), Payment[Date].[Month] = MAX ( 'Date'[Date].[Month] ) )
)

VAR b =
    CALCULATE (
    SUM ( Payment[Paid Amt] ),
    FILTER (  Payment , Payment[Date].[Month] = MAX ( 'Date'[Date].[Month] ) 
                && ( Payment[Date].[Year] = year_
                || Payment[Customer] = customer )
        )
    )
RETURN
    IF (
        ISFILTERED ( 'Date'[Date].[Year] ) || ISFILTERED ( Invoicing[Customer] ),
        b,
        a
    )

     

    Balance = 
VAR LastYearMonthNo =
    MAX ('Date'[YearMonthNo])
VAR MonthToSum =
    FILTER (
        ALLSELECTED (
            'Date'[Month],
            'Date'[YearMonthNo]
        ),
        'Date'[YearMonthNo] <= LastYearMonthNo
    )
RETURN
SUMX(MonthToSum,[Invoicing]-[Payment])