AGGR function for the sum of whole month with date field restricted?

[Serkan_U]

Hi everyone,

I have table consisting of customer_id,date and account balance. The business unit requests that the date field has to be restricted for last three months from the current selection, and it should be set to "always one selected value". So user can select only one date and that date goes back to 3 months.

This is the sample data:

What I need is to create new field which display the sum of the month, ie. for Jan'21 it should be 10.000,forFeb'21 it should be 4.200 so on.

I used aggr function with nodistinct  because I read sometime ago, if you use less dimension in aggr than in your chart, because of the granularity, only first row is filled.

To do this, I wrote AGGR(NODISTINCT SUM({<Report_date={">=$(=AddMonths(max(Report_date),-3))<=$(=max(Report_date))"}>} Accnt_Bal),Report_date). 

However this formula filled only the selected date again. You can see from below screenshot, AGGRNODISTINCTcolumn is where the formula is written

I also tried sum total formula which gave me the sum of the last three month  for every row, you can see it in the last column.

If I remove both date restrictions I mentioned above, aggr nodistinct calculates correctly.

Is there anyway to calculate monthly sum with the requested date rules?

Thanks

[brunobertels]

hi 

May be this : 

AGGR(NODISTINCT SUM({ 1 <Report_date={">=$(=AddMonths(max(Report_date),-3))<=$(=max(Report_date))"}>} Accnt_Bal),Report_date)

[Serkan_U]

@brunobertels , It did not work, gave me the same result.

[brunobertels]

Hi 

Sorry my bad 

try This measure instead :

SUM({ $ < Report_date={">=$(=AddMonths(max(Report_date),-3))<=$(=max(Report_date))"}>}total<Report_date> Accnt_Bal)

by default without selecting date it will display only 4 last months : 

then by selecting a date : 

Eg : 31/03/20021

Eg 31/05/2021 : 

hope it helps 

[Serkan_U]

That solved my problem,

thanks a lot

[brunobertels]

Nice 🙂 

would you please then closed your post by clicking "Accept solution" so that it marks the post as Solved 

Thanks in advance