DAX "running balance" measure displays values on wrong date level

[Anonymous]

Hi Everyone,

I've read through a lot of posts in this forum, but I still don't understand what I'm doing wrong.

1) I created a Calendar table with CALENDARAUTO() with Year, Month, Day, Today, DatewithTransaction calculated columns.

2) I loaded a basic table with sample financial data having Date (DD/MM/YYYY), Category, Value columns.
3) I also linked Date in the two tables and set my calendar as auto date.

4) Then, I created the following measures:
Income = CALCULATE(SUM(SampleTable[Value]), FILTER(SampleTable, SampleTable[Category] = "Income"))
Expenses = CALCULATE(SUM(SampleTable[Value]), FILTER(SampleTable, SampleTable[Category] <> "Income"))
MonthlyBalance = [Income] - [Expenses]
RunningBalance = CALCULATE (SUMX(SampleTable, SampleTable[MonthlyBalance]), FILTER (ALL(Calendar[Date]), Calendar[Date] <= MAX (Calendar[Date])))

PROBLEM: The RunningBalance works incorrectly. It is only willing to display data on day level. However, the financial data in my table is on month level. (Day is always 01 in column Date, so I have 01/01/2017 and 01/02/2017, but I don't have 17/01/2017 or 27/02/2017. In other words, transactions are only made on the 1st day of the month.)
The attached screenshot shows that the MonthlyBalance measure displays data correctly on month level, but RunningBalance duplicates the values for each day of the month. I would like that RunningBalance show me 1 value for each month instead of 30, and on month level instead of day level.

Thank you for any hint.
Bests, Valkofeeniks

Report settingsTable Structure

[stevedep · Accepted solution]

Hi,

It should be working, just be sure to use the date hierarchy and ensure you have the data table linked:

Power BI file is here. 

Please mark as solution if this helped you. Kudos appreciated..

Kind regards, Steve. 

[Anonymous]

Hi,

thank you for the answers and the input. It started working when I recreated my date hierarchy. So it must have been a wrong setting.

Thank you, Valkofeeniks

[vivran22]

Hello @Anonymous ,

I have used the following sample data :

Post transformation and adding a calendar table and creating a relationship on date

, I have used following measures:

Income = 
SUMX(
    FILTER(
        dtTable,
        dtTable[Type] = "Income")
        ,dtTable[Value])

Expense = 
SUMX(
    FILTER(
        dtTable,
        dtTable[Type] <> "Income")
        ,dtTable[Value]
    )

Balance = [Income] - [Expense]

Running Balance = 
VAR _MaxDate = MAX(dtTable[Date])
VAR _Filter = 
    FILTER(
        ALL(ftCalendar[Date]),
        ftCalendar[Date] <= _MaxDate
    )
VAR _RT = 
    SUMX(_Filter,
    [Balance]
    )

RETURN
_RT

Result

Cheers!
Vivek

If it helps, please mark it as a solution. Kudos would be a cherry on the top 🙂
If it doesn't, then please share a sample data along with the expected results (preferably an excel file and not an image)

Blog: vivran.in/my-blog
Connect on LinkedIn
Follow on Twitter