Simple problem driving me crazy - help!

Anything related to PowerPivot and DAX Formuale
Tantalus86
Posts: 1
Joined: Sun Apr 14, 2019 7:40 pm

Simple problem driving me crazy - help!

Hi,

I've been stuck on this little problem that I'm sure has a simple solution to it, however I just can't seem to crack it.

I have a date table, a budgets table and a table that contains transactions that are assigned to a budget. Here is some example data to see what I mean:

Transaction Table
Date // Budget // Cost
01 May 2015 // Budget A // -52
01 May 2015 // Budget B // -100
04 May 2015 // Budget A // 54.26
07 May 2015 // Budget C // -86.2
07 May 2015 // Budget C // -426.85
14 May 2015 // Budget A // 595.23
18 May 2015 // Budget D // -65.75
21 May 2015 // Budget A // -139.87
...etc

Budget Table
Budget // Initial Balance
Budget A // 10000
Budget B // 8500
Budget C // 19000
Budget D // 5000

What I'm trying to do is to show what the current balance is of each budget, based on the transactions that have taken place. (i.e. the initial balance + sum of transactions for that budget.

Where I'm coming unstuck is that I need to include a date slicer but the calculated budget balances needs to show the balance of each budget at the end of the period - not just the transactions within the date range from the slicer (the transactions date back to May 2012)

So for example, if the date slicer filtered data between 01 October 2016 and 31 October 2016, I'd need to know what the current balance of each budget is as of 31 October 2016. Whenever I try this with a measure, I end up getting the balances based on either the transactions between 01 and 31 October, or based on ALL of the transactions (up to today).

So the trouble I'm having is trying to sum the transactions for the right date range. This is my current DAX but this only works if the date slicer starts on 01 May 2015. I'd like the measure to ignore the lower date of the slicer.

{L_CODE}{L_COLON} {L_SELECT_ALL_CODE}

`Budget Balance = CALCULATE (    SUM (Transactions[Cost]),    FILTER (        ALLSELECTED('Date'),        'Date'[Date] <= MAX ('Date'[Date])    ))`

Any ideas? I'm sure it's just using the right combination of calculate, sum, filter and all - but I can't get it to work!

PhilC
Posts: 274
Joined: Tue Sep 09, 2014 8:13 am

Re: Simple problem driving me crazy - help!

Hi Tantalus86,

Have you tried your measure with ALL instead of ALLSELECTED? You want to capture all transactions that have ever occurred prior to the last date of the slicer. ALLSELECTED will only include transactions that occur between the start and end dates of the slicer, so you need to use ALL to access all dates. The 'Date'[Date] <= MAX ('Date'[Date] should then limit it to the MAX date of the slicer time period (I think).

The measure you have shown would not be the balance though, as you are not bringing in the initial balance. Maybe create a measure for that as well, then the balance would be addition of the two measures.

Post a workbook if the above does not work, as that will provide something for us to play and test with.

Cheers
Phil