Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I am using the below condition in a table but the if condition is making the reload extremely slow, basically I am filtering my records on a dimension
=if(num(NumDate) >= num( MakeDate(2021,4,1))
and num(NumDate) <=num(monthend(AddMonths(monthend(today()),-1))),[Selling Unit])
Now I am implementing the same condition in my measure using Set Analysis but I am getting wrong results
Sum({<DateBridgeFlag = {'Procurement'},NumDate={'>=$(num(MakeDate(2021,4,1)))<=$((=(num(monthend(AddMonths(Today(),-1))))))'}>}[Received Qty])
Basically it covers a period from 01 April 2021 to 31 March 2022
Please help
Hi @risabhroy_03,
If your measure is in the same object as your dimension then the set analysis will calculate the appropriate dimensions values. No need to add an if condition.
You also have a few nested date functions that we can omit as the monthstart( ) function has a built in parameter to shift the months. Deleting these will mean less computation. I assume NumDate is a numeric integer value.
Sum({<DateBridgeFlag = {'Procurement'},NumDate={">=$(=num(MakeDate(2021,4,1)))<$(=num(monthstart(Today())))"}>}[Received Qty])
Your <= monthend( ) functions return the last second of last month which currently is: 44681.999999988
I've changed this to < Monthstart( ) as this only returns 44682
If you still need the dimension condition it would look like this:
=if(num(NumDate) >= num( MakeDate(2021,4,1))
and num(NumDate) < num(monthstart(today())),[Selling Unit])