Stopping rangesum showing after a given date

[rakeshshah]

Hi

I am using rangesum function to plot Forecast vs Actuals along the same date line. When i run the Actual sum range, it works by restricting the date based on the set analysis (see formula below). However I want to also show forecast numbers for the next couple of weeks after the actuals have ended (ie "today"). When I do this it shows the actuals as a flat line across those dates (see image). Is there a way to stop the actuals showing for dates "after today"

Actual: rangesum(above( sum({<[Date]={"<$(Today())"}>}[Actual_deployment]),0,rowno()))

thanks in advance

Rakesh

[devarasu07]

Hi,

You can try like below,

Rangesum(above(sum({$<DATE = {'$(=Date(Today() ))'}>}[Actual_deployment]),0,rowno()))

or

Rangesum(above(sum({$<Date = {"=$(=Date(Today(), 'MM/DD/YYYY'))"}>}[Actual_deployment]),0,rowno()))

Thanks,

Deva

[rakeshshah]

Hi Deva, thank you for your reply. This sort of works... as the only measure in the graph it will reduce the dates dimension to less than today (btw had to a '<' into your formula)... however when you add the forecast measure it adds the future dates in (as expected) and the line flattens across. Trying to remove the flattening line on future dates.

Thanks

Rakesh

[devarasu07]

Hi,

after adding the less than Today. is it working?

Rangesum(above(sum({$<Date = {"<=$(=Date(Today(), 'MM/DD/YYYY'))"}>}[Actual_deployment]),0,rowno()))

[rakeshshah]

the set analysis is working, but its not displaying how I want it to.

will demonstrate with images

How it is currently showing

How I would like it to show

[devarasu07]

Hi,

can you send me forecast  expression? also would be able to share your mock data with app. thanks

may be u can try this

Less than today set condition apply only your  actual measure field

forecast just remove that set analysis part like below,

Rangesum(above(sum(Forecast_Amt]),0,rowno()))

Thanks,

Deva

[OmarBenSalem · Accepted solution]

try; if (sum([Actual_deployment])=0,null(),

rangesum(above( sum({<[Date]={"<$(Today())"}>}[Actual_deployment]),0,rowno())))

this will work

[rakeshshah]

Awesome thanks Omar and Deva for your help.

The combination has resolved it - but namely the logic of if(sum)