Previous Month Comparison when Previous Month has Fewer Days(Indicator Widget)

[viswanath]

Hello Everyone,

We have come across one requirement for one of our customer, where days in Date as Dashboard filter and in an indicator widget primary value represents "count(Ids)" and in secondary value "pastmonth(count(Ids))". When we select filter like 12/31/21, pastmonth() will get the value of the same day of the previous month. As there is no 31st Day in 11th month, it is showing us '0'. But instead it has to show us 30/11/21 value in the secondary value of indicator widget when we select 12/31/21 in the filter. If anyone come across this kind of usecase can share your thoughts. Thanks in advance.

[harikm007 · Accepted solution]

Hello @viswanath ,

Check whether this formula gives you expected result. Here, if the value of PASTMONTH is blank or 0, it will look for a date 31 days ago.

case when isnull(max(PastMonth(count([id])))) OR max(PastMonth(count([id]))) = 0
then max((count([id]), prev([Days in Date], 31)))
else max(PastMonth(count([id])))
end

[viswanath]

That’s really helpful, Hari. Thanks a lot.