Exclude future values on line-chart with accumulation on the measure

[krzys]

Dear Community,

I came across an issue I feel I am close to solving, but actually, this might not be possible to solve.

I want to have two lines on a line chart showing the accumulated growth per week for my measures. Ideally, I would like the two lines to be as close to each other. This will mean that we are good on the track.

Sample data is 2 columns (attached)

In the chart underneath, I use:

Measure 2020:  Count(distinct {<"=Dual(Year([Date]),YearStart([Date]))"={'2020'}>}ID]) - modification on Accumulation full

Measure 2021:  Count(distinct {<"=Dual(Year([Date]),YearStart([Date]))"={'2021'}>}ID]) - modification on Accumulation full

Dimension:  [Date.autoCalendar.Week]

The above chart is close to perfection, but I would like to achieve the same but without 117 for all the future weeks. I would rather this line be visible (without accumulating, these would be just zeros, but I would not see the growth) unless you have a way to show forecast instead of 117 for future weeks. That would be even better.

Do you have any ideas?

[Ksrinivasan]

hi,

you can try this logic to avoid future value of 117,

if(Date_week<= Week(Today), Count(distinct {<"=Dual(Year([Date]),YearStart([Date]))"={'2021'}>}ID]) ,'')

ksrini.

[krzys]

Thanks for the tip @Ksrinivasan 

I did try that before, and it works well if there is no accumulation. The chart shows nothing instead of zeros. 

But once I turn on the accumulation to see the growth, the result is the same.

Your idea will work if I have a way to precalculate accumulated values in a separate table... Or somehow exluce Max vales?

[Ksrinivasan]

hi,

thank for your reply, and good to hear,

you can use same logic for 

if(weekday<= week(today),Cummulative ,Cumulative *0)

ksrini.

[krzys]

hey again @Ksrinivasan 

The result for accumulation is the same but without accumulating is worse as zeros appeared again

I used this

if([Date.autoCalendar.Week]<= Week(Today),Count(distinct {<"=Dual(Year([Date]),YearStart([Date]))"={'2021'}>}[ID]) ,Count(distinct {<"=Dual(Year([Date]),YearStart([Date]))"={'2021'}>}[ID]) *0)

In principle it is correct that accumulation is giving these values. It takes MAX until data is reported and they just add zeros until the end of axis X.

Unless you had something else in mind?

[Ksrinivasan]

hi,

ok, try it,

if([Date.autoCalendar.Week]<= Week(Today),Count(distinct {<"=Dual(Year([Date]),YearStart([Date]))"={'2021'}>}[ID]) ,(Count(distinct {<"=Dual(Year([Date]),YearStart([Date]))"={'2021'}>}[ID])-Count(distinct {<"=Dual(Year([Date]),YearStart([Date]))"={'2021'}>}[ID]))

ksrini

[krzys]

Hey, unfortunately, it is not it. I get incorrect values for both: accumulated and regular values

[Ksrinivasan]

hi,

can you share your qvf.

ksrini

[krzys]

sure @Ksrinivasan 

QVF and data attached

[krzys]

hey @Ksrinivasan 

It seems it not possible...(?) - thanks for trying