Using Date in Set Expressions

[Atx3765]

I am trying to generate a KPI on market penetration rate given the current month. The one set expression I cannot solve is to calculate on the values only for the current month. Prospect Data Added is structured as 'MM/DD/YYYY' and I want to report on values only with the current month.

=SUM({<[Market2(FD)]={'North America'},[Prospect Data Added]={"$(=date(month(today(1)),'DD/MM/YYY'))"}>}[Prospect(FD)])/SUM({<[Market2(FD)]={'North America'},[Prospect Data Added]={"$(=date(month(today(1)),'DD/MM/YYY'))"}>}[All Deals(FD)])*100

What is wrong with the above formula?

[abhijitnalekar · Accepted solution]

Hi @Atx3765 ,

Please update your expression as below.

SUM({<[Market2(FD)]={'North America'},[Prospect Data Added]={"$(='>=' & Date(monthstart(today()), 'MM/DD/YYYY') & '<=' & Date(monthend(today()), 'MM/DD/YYYY'))"}>}[Prospect(FD)])
/SUM({<[Market2(FD)]={'North America'},[Prospect Data Added]={"$(='>=' & Date(monthstart(today()), 'MM/DD/YYYY') & '<=' & Date(monthend(today()), 'MM/DD/YYYY'))"}>}[All Deals(FD)])
*100

make sure that [Prospect Data Added] format is 'MM/DD/YYYY'.

[Atx3765]

This worked thank you very much for replying!