Modify measure formula to show distinct value (only if same item NO)

[Anonymous]

My measure formula:

Sum(distinct(if(Gate='Gate D' and Status='Completed' and Temp='Task_17'and

[Date Modified.autoCalendar.YearMonth]>= (AddMonths(MonthStart(Today()-20),-11))

,[SOW Value])))

Value shown = 18,619,846

How to modify my formula in order to get 19,230,824 ?

[raman_rastogi · Accepted solution]

Try this

sum(aggr(Sum(distinct(if(Gate='Gate D' and Status='Completed' and Temp='Task_17'and

[Date Modified.autoCalendar.YearMonth]>= (AddMonths(MonthStart(Today()-20),-11))

,[SOW Value]))),SR No))

[mdmukramali]

Hi,

can you  try this

=Sum(

{<

          Gate={'Gate D'} ,Status={'Completed'},Temp={'Task_17'},

           [Date Modified.autoCalendar.YearMonth]={">=AddMonths(MonthStart(Today()-20)-11)"}

>}

[SOW Value])

or

=Aggr(

Sum(

{<

          Gate={'Gate D'} ,Status={'Completed'},Temp={'Task_17'},

           [Date Modified.autoCalendar.YearMonth]={">=AddMonths(MonthStart(Today()-20)-11)"}

>}

[SOW Value])

,No)

[Anonymous]

Thanks Raman. Its works.