Forum Discussion
Need help optimizing measure
Hi Anonymous
Try this:
Measure =
VAR _Start =
CALCULATE ( MIN ( DateTable[CalendarDate] ) )
VAR _Finish = _Start - 180
VAR _CTP =
CALCULATETABLE (
VALUES ( [email] ),
FILTER ( DateTable, DateTable[CalendarDate] = _Start )
)
VAR _NT =
ADDCOLUMNS (
_CTP,
"TT",
CALCULATE (
COUNTROWS ( vCustomerOrders ),
FILTER (
ALL ( DateTable ),
DateTable[CalendarDate] >= _Start
&& DateTable[CalendarDate] <= _Finish
)
)
)
VAR _FNT =
FILTER ( _NT, [TT] >= 2 )
RETURN
COUNTROWS ( _FNT )
It would be better if you share a sample of your data here in a text format.
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Hi VahidDM,
Thanks for your recommedation! I just tried your code but it's not returning anything. Here is the data in my vCustomerOrders table looks like:
If the date on my report is set to 3/14/2022, CustomerC and CustomerD will be repeating customer because the first order the date of their previous order is within the last 180 days. CustomerB is not repeating because he has only one order. CustomerA is also not repeating because the previous order date is more than 180 days ago.
Anonymous Can you copy and past your sample data here as a text to be able to cupy and past them into PBI?
Also, can you let us know how do you want to show the result?
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/