Can't get cumulative AR Balance

Power Query
1

I am having trouble calculating the cumulative beginning AR balance for a given year. I believe it has something to do with my beginning AR balance formula which is the first calc below.

Beginning AR Balance = calculate( [MTD Outstanding AR], PARALLELPERIOD( Dates[Date], -1, month))

This is correctly populating the beginning AR balance (prior month-end balance). I use the following formula as an attempt to calculate the cumulative total beginning AR balance (the sum of beginning balances for the periods selected), and it will not give me the cumulative, rather it simply mirrors the value given from the above formula.

Total Beginning AR = calculate( [Beginning AR Balance], 
                        FILTER( ALLSELECTED( Dates ),
                        Dates[Date] <= max( Dates[Date] ) ) )

Could this have something to do with the parallel period piece of the first function? Hope someone can help!

Thanks
Nick

2

Try using DATEADD instead of PARALLELPERIOD. Here is a video from Sam on DATEADD.

Let me know if this helps. If you still have issues, please post a sample of your PBIX file so that we can better answer your question.

Thanks
Enterprise%20DNA%20Expert%20-%20Small

3

Thanks for the reply! I actually just watched that video this morning and tried the dateadd method and it populated the same result. What is the best method for sharing the PBIX file? Below is a snip-it of a table and shows the filters. The beginning AR balance is the prior month-end value and is used to calculate collection %. I need the total beginning AR balance to calculate YTD collection %. October is the first period of the fiscal year. The prior year-end balance needs to be carried over (September month-end balance)

image
image.png1272×360 24.1 KB

image
image.png714×36 2.21 KB

image
image.png695×40 2.51 KB

image

image

4

Just upload your PBIX file when replying to this post by clicking on the icon in the picture below. Depending on the size, you may have to upload as a Zip file. Also provide the correct answer to the column(s) that are in question.
image

Thanks
Enterprise%20DNA%20Expert%20-%20Small

5

Branch Goals Scorecard.pbix (275.9 KB)

image
image.png1168×214 12.1 KB

The total beginning AR column should be calculating like the cumulative AR column. which is just the cumulative beginning AR balance. Thanks for your help! And let me know if you have any problem getting into the PBIX

6

One of the issues is that you were using the [MTD Outstanding AR] measure instead of the [Total AR] measure. Here is my solution. In order to create the Cumulative AR that is highlighted in green in your last response I created the following 2 measures:

DATEADD Beginning AR Balance =
CALCULATE ( [Total AR], DATEADD ( Dates[Date], -1, MONTH ) )

Total Beginning AR =
CALCULATE (
    [DATEADD Beginning AR Balance],
    FILTER ( ALLSELECTED ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )
)

Here is what it looks like:

image
image817×270 18.9 KB

Thanks
Enterprise%20DNA%20Expert%20-%20Small

1 Like
7

Thank you so much for the help! I was stuck on this for WAY too long. Much appreciated!

Thanks,
Nick

1 Like