Try to achive running count of ids for current month to last year same month

[pranaligawde]

Hello All,

Try to achive running count of ids for current month to last year same month.but without restriction.Asi want to showfor current year month till previous year same month.But it should conider all the values before current month .bit it is considering from selected month only.

Dimension :MonthYear

Measure:

RangeSum(Above( total count({<period={">=$(prevyeardate)<=$(curryeardate)"}>} distinct Management_Escalations_Open_Cases_for_balance_CI)

,0,RowNo()))

[pranaligawde]

Hello @tresesco 

Without any selection i need to show on bar chart monthyear from jun19 till may20 and it should get accumulated value  from 11 months back which is jul 19.

[tresesco]

Try exp:

rangesum( above( sum( {<Month_Year1={">=$(=Date(MonthName(Max(Month_Year1),-11),'MMM-YY')) <$(=Max(Month_Year1))"}>}  Quantity),0,11) )

without accumulation in chart setting.

[pranaligawde]

Hello @tresesco ,

i achived output with this set anaysis .

vPeriodType=   '<=$(=Date(MAX(Month_Year1),'MMM-YY')) >=$(=Date(AddMonths(MAX(Month_Year1),-11),'MMM-YY'))'

=if(
Count({<Month_Year1={"$(vPeriodType)"},Month=,Year=>}[Complaint ID in Source]) <> 0 ,
RangeSum(Above(Count(distinct [Complaint ID in Source]), 0, 12)))

But now my user ask is if 12 months are not present while calculatin 12 months rolling then in that monthyear value suppose to 0.

for eg: In chart  it is showing 12 month from jun19 to May 20 in that case if there is no sufficient month before jun19 (i.e 12 month befroe jun) then jun19 value should be zero.And chart should calculate other month value as per 12 month logic.

 plz advise how i can alter my formula to achive this.

appreciate your help in advance.