Next 7 days receivable KPI

[Manisha1407]

Hi Team,

I want to make a KPI to see amount receivable/due in next 7 days or 30 days

Data as follows:

Invoice Number	Document date	Duedate	Rbalance
CIN1010110	24-05-2023	24-05-2023	5000
CIN2039291	24-05-2023	31-05-2023	15000
CIN3446462	24-05-2023	15-06-2023	26000

I am using the below set analysis but 0 is coming as result. (Result should be 20,000)

=sum({<DueDate = {">=$(=Today())<=$(=Today()+7)"}>}RBallance)

Can you please advise where i am going wrong and what should be the best solution to get this KPI.

[Chanty4u]

Try this 

=sum({<Duedate = {">=$(=Date(Today(), 'DD-MM-YYYY'))<=$(=Date(Today()+7, 'DD-MM-YYYY'))"}>} Rbalance)

[Manisha1407]

Hey!! thanks for the reply but still the same result.

[Manisha1407]

Note: 

Rbalance is not a measure, its a field.

Is this a reason for 0 value.

However if i am making KPI as Sum(Rbalance) it is showing correct value.

[Chanty4u]

just try only this and tell me the results

in the expression editor on bottom you will see the expresion results.

=sum({<Duedate = {{">= $(=Date(Max(Duedate)+7, 'DD-MM-YYYY'))"}>} Rbalance)

[Manisha1407]

Hi,

Result with your original suggestion.

Result with your second suggestion.

[Chanty4u]

in your first expression  you can use  +7   instead of  +0  and try .?

[Gabbar]

In script.


in expression:-
=sum({<Duedate={">=$(=Date(Today(),'DD-MM-YYYY'))<=$(=Date(Today()+7,'DD-MM-YYYY'))"}>}Rbalance)



output:-

Set as today so 5000 of 24th may not coming

[Manisha1407]

Hello,

The expression is showing as Okay but the data is still not coming. Rballance is a field not a measure, is it because of that.

[Gabbar]

Please use that expression as a measure, it should work and is it doesnt,

please take a picture on your data model viewer where you have clicked on DueDate, I want to know its data type, it need to be timestamp.