I want to get the total number of all records that is expiring this month.
I have two tables Dashboard and Materials. The two atbles are connected with a relationship of ID Constant = id Constant which are both calculation with a result of 1 and Constant today ( Get(Currentdate), Unstored and Expiry Date (Date)
In my Dashboard I have also a field Expiring this Month with a calculation
[AlertCount = Count ( Materials::id Constant ) ] ;
AlertCount = 0 ; TextColor ( "0" & " Material expiring today! " ; RGB ( 120 ; 120 ; 120 ) ) ;
AlertCount = 1 ; TextColor ( AlertCount & " Material expiring this month! " ; RGB ( 0 ; 136 ; 0 ) ) ;
AlertCount >1 ; TextColor ( AlertCount & " Materials expiring this month! " ; RGB ( 220 ; 0 ; 0 ) ) ;
to give me the number of records that is expiring this month, in my dummy records I have 5 materials that is due for this month but it showing me only 1 Number of material that is due for this month.
I have attached the file for quick reference.
Any help is highly appreciated.
Thank you so much