Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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'.
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'.
This worked thank you very much for replying!