Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the following expression which gives me the total count of the number of clients over a fortnight period (current week + previous week):
count(distinct{<[Shifts.CreatedStartDate] = {">=$(=Date(WeekStart(Today()),'DD/MM/YYYY'))"}>}[Client])
+count(distinct{<[Shifts.FULLPRW]={1}> }[Client])
Now I need to find the number of distinct clients over the fortnight. Any ideas on the expression?
Thanks
May be this?
Sum(DISTINCT Aggr(count(distinct{<[Shifts.CreatedStartDate] = {">=$(=Date(WeekStart(Today()),'DD/MM/YYYY'))"}>}[Client]) +count(distinct{<[Shifts.FULLPRW]={1}> }[Client]), Dim1, Dim2))
Hi Anil,
Tried the above. It's not giving the right result.
Thanks
Not sure, Would you able to provide sample
I don't have a sample as such.
Just looking at the data again, I can see that fortnight periods have been defined and values e.g shifts created for each fortnight.:
Fortnight No
3/11/2017 90
8/10/2017 100
24/09/2017 95
10/09/2017 97
What would the set expression be to get the numbers for the latest fortnight ? There are future fortnight periods in the data which I want exclude. e.g.3/11/2017. So I am trying to get an expression which will show me the figures for the latest fortnight only i.e. 8/10/2017.
Thanks
This case, My suggestion always go with variable, Like?
let vTodayDate = Date(Today());
And expression is like
Sum({<Fortnight = {"<=$(vTodayDate)"}>} No)
Hi,
Can you try like this?
=SUM({<Fortnight ={">=$(=MonthStart(Today())) <=$(=MonthEnd(Today()))"}>} No)
Thanks,
Deva
Hi Devarasu,
I tried your expression it works. What happens when your fortnight period is in a different month than today? I think your expression only picks the dates in the relevant month. Correct me if I am wrong.
Thanks