Column that indicates if item was sold during period

In some analysis I need to filter the items that were sold or not during period. I created simple column that must indicate it
IS_Sold = IF (OR(OR([Sales Amount]=BLANK(); ISBLANK([Sales Amount]));[Sales Amount]=0) ; “NO”; “YES”)

No works well
but if I select YES I have in list sold and not sold items

For this I would probably simplify it a little bit by making sure no sales is equal to zero first of all.

This way you formula can be much simpler.

IF( Yes is selected, CALCULATE( Total Sales, Total Sales > 0 ), CALCULATE( Total Sales, Total Sales = 0 )

Something like the above.

You might have to play around a little with this, but this is the logic you need to create.

[quote=“sam.mckay, post:2, topic:2371”

I think that suggested function result will be same as Total Sales. In both cases function return Total Sales. I need boolean field in slicer to filter products list that was sold or not during period. There is little bit simple variation of this function IS_Sold = IF (OR(ISBLANK([Sales Amount]);[Sales Amount]=0) ; “NO”; “YES”) . It returns NO if product isnt’t sold during period, so it is ok. But it return Yes for some products that isn;t sold as well.

Going to need a demo to understand more about this to offer better assistance here.

I recommend setting up a demo model with the scenario in it so that is can be reviewed better.


During preparation of demo data I resolved this issue myself. All logic was

In Operation table I have all stock operations sales, purchases, returns etc. All incomes are with positive numbers, all outcomes are negative

Measure Sales Amount I calculate with simple function SUM ( ), and hope that report slicer Where Op_type = Sales, will filter only sales operation. In most cases it works, but not always correctly.

When I change measure calculation to Sales_ Amount = SUMX ( FILTER(Stock_Operation, Op_type=“Sales“), Amount) it started to work absolutely correctly. I could easy filter all items that was sold or not during period using column IS_Sold = IF (Sales_Amount=0; “NO”; “YES”)

Ok great