Opening Balance Issue when there are gaps in months

DAX DAX Calculations
1

Hello - I am having trouble getting the opening balance to populate when there are gaps in transaction activity.

If you look below, there is no activity for the month of November. I think this is causing the opening balance not to carry from October to November, and the result is that it is not pulling it forward into December. Can someone assist?
image

Formulas
image

image

Thanks!

2

Hi @nickjordan32,

See if this works for you:

Opening Balance projection =
VAR PreviousDate  = 
CALCULATE( MAX( Dates[Date] ),
    FILTER(
        FILTER( ALL( Dates ), Dates[Date] <= MAX( Dates[Date] ) ),
        NOT( ISBLANK( [Opening Balance] ) )
    )
)
RETURN

CALCULATE( [Opening Balance],
    FILTER( ALL( Dates ), Dates[Date] =  PreviousDate
    )
)

There might be something else going on because the OPENINGBALANCE functions use the FIRSTDATE of the considered period internally, so let me know what happens…

1 Like
3

Melissa, It appears that this worked! Couple more questions for you:

  • Will this negatively impact performance? It seems that the wheels were spinning a bit before the visualization updated.
  • Do I need to test differing gaps in time, or do you think this code should be a solution to all types of related gaps in time?

Thank you!

Nick

4

Hi Nick,

The PreviousDate variable calculates the last date the [Opening Balance] wasn’t blank from that day’s perspective and does the same for every row in your table/matrix.
Because you always get a PreviousDate, which by the way is equal to the Dates[Date] for each non blank [Opening Balance], you should be fine.

Now there are multiple FILTER statements required so that does come at a higher cost than the basic [Opening Balance] measure you had previously. But I’m not seeing any way around that.

5

Hey Melissa - Is there any way I could change this to provide only the opening balance projection by month? The formula worked to accomplish the gap issue but it seems it has severely impacted the performance and speed of the report. I am not an expert, but I have a feeling the opening balance projection pulling forward every day is a big factor. This report will only look in month increments, so if the daily pull forward is impacting the model, I would like to see about optimizing if possible. Let me know your thoughts. Thank you!

image
image.png835Ă—595 23 KB

6

Hi Nick,

Not quite sure what you’re after here. Your table is now at a daily granulairity, do you intent to switch that to a monthly level? Or do you still want a daily level in your visual but only calculate the [Opening balance] for each first of the month and show that value on all other days within that month?

Please provide some more context and a sample in PBIX would be helpful too.

7

Hi Melissa,

Daily level granularity is not necessary as we are just looking at the month end results for this analysis. If there is a way to optimize the code to improve model performance that would be ideal.

Thoughts:

I think the code is significantly impacting the performance of the model (speed of loading and filters; wont even load on cloud service and maxes out computer CPU), because it is calculating the opening balance by day for tens of thousands of unique “projects”. I think it is creating a virtual table with 10’s of millions of rows. It seems like there might be a way to reduce the # of iterations which (hopefully) would help performance. If this is not enough information, I can sanitize the model and send over. Let me know, and thank you very much for your help.

Thanks!

Nick

8

Melissa - I just did some performance analyzing and found that it is a different measure that is bogging down the model (thankfully, as the measure solution you provided is critical to analysis). Thank you for the solution.

9

Great, ok thanks for the feedback Nick.

1 Like
10

I did want to share one item on the performance of the model and the solution I came up with (workaround to adjusting the DAX formula, since I’m a newbie). I continued working on performance improvement of the formula. Since I just needed the balance to carry into the next month (not every day), I went ahead and just altered my date table to only include the month-end date of every month, thereby reducing the size of the date table (from almost 2K to only 84). This in turn, significantly reduced the # of iterations that the formula had to go through to accomplish the task. Another thing I need to look at doing is stop the formula from carrying an opening balance into months into the future (as there is no need for it to show an opening balance for anything past the current month). I realize I didn’t provide enough context/information for you to be able to help on that but we can chalk it up as a personal win!

Again thank you for your help and if you have any further suggestions or thoughts let me know!

Thanks,

Nick

2 Likes
11

Melissa - Another item came up that I was wondering if you could weigh in on… Is there any way that I could get the opening balance projection the populate on my fact table? I have attempted to do so, but it comes up as a blank column… Any thoughts? Let me know if you need additional info.

Thanks!

Nick

12

Hi Nick,

In your fact table you would not need to project because you will have a (date) value for each row… anyway please check out the article below