How to apply filters on accumulated amount (this case: outstanding AR Total)?

[JustinDoh1]

Hi Community,

(I am sharing my Pbix file here )

I am trying to apply slicers (filters) on an accumulated amount.

I am doing A/R aging analysis, and requirement is apply filter(s) on oustanding A/R amount.

Initially, I was able to apply filter on a monthly amount (not oustanding amount) because, I added a month index column ("Year-MonthName") on the Calendar table and joined with data with a Fact table (where it contained the data ("PayerNameRedefined") to be used for a filter.

This is data modeling:

Let me show you what I am trying to explain:

This one is A/R of each month (not accumulated (outstanding), and filter works fine.

Filter works fine:

** This is Requirement/Expectation:

Just for FYI, I have a separate data to calculate "Total AR As of Month" on a table called "MonthsTable", but this one does not have a relationship with data for filter (in the Fact table ("vw_revenue_metrics_ar_aging").

Is there any function in PowerBI I could use to get the "outstanding amount of each month / accumulated amount as of certain month" using the data in the Fact table ("vw_revenue_metrics_ar_aging")?

Thanks.

[amitchandak · Accepted solution]

@JustinDoh1 ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Usually get cumulative like

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

or

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

[JustinDoh1]

@amitchandak

Thank you for help!

I am sharing here (as indicated on the original post).

Please let me know if you have any issue opening the file.

Thank you.