Using MonthEnd in If Statement

[cbaqir]

I have a graph (attached) that shows Actuals in a solid line and then turns into a dotted line for Forecasted hours.

The actuals get posted on the last day of the month so since it is currently July, I would expect to see the solid line end in June. Instead, it's showing through the end of this month. I tried to overcome this by using:

Dimension: =date(FISCAL_MONTH_YEAR, 'YYYY-MMM')

Expression:  if(FISCAL_MONTH_YEAR>Monthend(today())+1,sum(FCST_AMT),sum(ACT_AMT))

Line Style: if([FISCAL_MONTH_YEAR]>today(),'<S3>','<S1>')

Even though the dotted line is starting in August now, my July date has disappeared. which shouldn't be the case.

How do I get the current month to display but the dotted line starting from the last month?

Thanks,

Cassandra

[sunny_talwar]

This?

[cbaqir]

Wow, that is complicated!

I've never used RangeSum(Above()) before. Thanks!

[cbaqir]

But when I applied this to my chart I still don't see July. Any idea why? I copied over the formulas verbatim.

[sunny_talwar]

‌I did change the expression around also. Try imitating the expression change as well.

[cbaqir]

I copied the expression and line style over completely. Have you ever seen one month of the dimension just disappear before?

[sunny_talwar]

‌HAhaha I have not... But it seemed like the Full Acumulation Dynamics with the if statement issue to me. have you moved the full accumulation back to no accumulation?

[cbaqir]

Good idea but unfortunately, it didn't fix it. If I change <= to only = in the expression, it shows up again but then I lose my historical data. I'm not familiar with all of the functions in this If statement but I will continue to play with it to see if I can find a combination that works.

If(If(FISCAL_MONTH_YEAR = Monthend(Today()), Sum(ACT_AMT), Sum(FCST_AMT)) <> 0,
RangeSum(Above(TOTAL If(FISCAL_MONTH_YEAR = Monthend(Today()), Sum(ACT_AMT), Sum(FCST_AMT)), 0, RowNo())))

[sunny_talwar]

I really can't say much without seeing what is going on because in the qvw I attached, it seems to be showing all months, right? I hope I am not missing a month by chance

[cbaqir]

Here's an updated sample with your expressions applied to my chart.