get each last month sum , and current date sum

[davinfrost]

hi guys , newbie here.
i want to get total sum on last date of month, when i filter month, but in current month the date still on going , it will sum on current date

for example , when i choose jan - oct
i want to get each last date of month(31jan, 28feb, 31mar)

and if i pick nov, but data only show on 1st nov, then it will show 29th nov, not 30th nov , (because November 31 hasn't been reloaded)

i've tried this but still doesnt work at all , ps: sry im very noob xD

if(isnull(RELOAD_DATE)=0,
(sum(
{<
FLAG_TRANS={'Outstanding'},
RELOAD_DATE={'$(=max(RELOAD_DATE))'},
Year={'$(=max(Year))'}
>}
OOWING)),


(
sum(
{<
FLAG_TRANS={'Outstanding'},
FilterDate={"=If(FilterDate=Text(Date(MonthEnd(FilterDate))), FilterDate)"},
Year={'$(=max(Year))'}
>}
OOWING
)
)
)

please help me guys..

thanks in advance

[Anil_Babu_Samineni]

The RELOAD_DATE is already there till 29th Nov only right? That means, Data will come till that point.

[Michiel_QV_Fan]

The answer is in your question. No data loaded for 31 nov because that date did not happen yet.

To make it visible you could create a subtotal in your script like this:

concatenate

LOAD

date(monthend(your_date_field)) as your_date_field,

additional fields,

sum(your field) as Monthly_total

resident your_fact_table

group by 

your_date_field,

additional fields;

This will create the last date of the month and the total of the month.