Calculating Lost Sales using Time Intelligence Functions

[AJ1969]

I have created a simple data model consisting of three tables: Customers, Calendar and Sales. I have a DAX measure that calculates lost customers, which I have defined as customers who did not ship in the current year (Year is in Row area of pivottable) and shipped in the prior year. It is:

Calculate(
COUNTROWS(Customers),
Filter(Customers,
Calculate(CountRows(Sales)) = 0
&&
Calculate(
CountRows(Sales), PARALLELPERIOD(
Calendar[Date],-1,Year))>0
)
)

I created this formula after viewing a video on ProjectBoticelli.com where Alberto Ferrari explained how to calculate new customers with this formula (his definition of a new customer is someone who shipped in the period being evaluated and did not ship in any previous period. His formula is:

Calculate(
CountRows(Customers),
Filter(
Customers,
Calculate(CountRows(Sales)) > 0 &&
Calculate (
CountRows(Sales),
Filter(
All(Calendar),
Calendar[FullDate] < Min(Calendar[FullDate])
)
) = 0
)
)

The first formula above appears to be returning the correct results but I would like to know if my syntax is correct.

Next I have tried to write another formula that calculates the lost sales, that is the sales made in the previous year to these lost customers. It is:

Calculate(
COUNTROWS(Sales),
Filter(Customers,
Calculate(
CountRows(Sales), PARALLELPERIOD(
Calendar[Date],-1,Year))>0
&&
Calculate(CountRows(Sales)) = 0

)
)

This formula is not returning any results and I admit that I'm not really sure what I am doing. It seems like I have to CountRows(Sales) but the Filter(Customers) doesn't make any sense to me.

Can someone please tell me whether or not my first formula is correct, and how to write the lost sales measure.

Regards

AJ1969

 

[Laurent C]

"The first formula above appears to be returning the correct results but I would like to know if my syntax is correct."

Well, if the syntax was not correct, PowerPivot would throw an error.

Commenting on whether your formula does what it is supposed to do would require more information about the requirements.

Regarding the second formula:
Calculate(CountRows(Sales)) = 0

You filter customers who had no sales within the current filter context. Consequently, COUNTROWS(Sales) for these customers will return BLANK.