Month Count Between two dates

Hello Team,

In dataset I have one datetime column - servicedate and graph where i am reprensenting servicedate(month) vs total amount
I want to calculate and show the 90 days back average of total amount from today as the reference line in Bar Graph for comparison.

for ex - service date - 18 dec 2024 so we calculate the total sum amount from 19 sept 2024 to 18 dec 2024 and to calculate Average we divide it by 3 as we want 90 days average in month wise.

calculated field = sum(total90daysamount)/3

but problem arises when adding this calculated field in the average reference line!!.
it again divide by 4 because (sep -1, oct -2, nov-3, dec-4).

If I tackle above Problem by changing calculated field to (sum(total90daysamount)/3)*4)

another problem arises when the current date will be 31st dec 2024 then 90 days before current date would be 1 october and month count is 3. Therefore, multiplication with 4 is wrong.

do we have other alternative ?? Please give your feedbacks
thanks

Hi @jaikji

Can you refer to this article?

I think you need to deal with the date filtering in the visual as well. So this article should give you the required details for the computation

Regards,
Giri

2 Likes