cancel
Showing results for 
Search instead for 
Did you mean: 

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

viswanath
8 - Cloud Apps
8 - Cloud Apps

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.

1 ACCEPTED SOLUTION

harikm007
12 - Data Integration
12 - Data Integration

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

 

View solution in original post

2 REPLIES 2

harikm007
12 - Data Integration
12 - Data Integration

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

 

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