Calculate the number of Items with filters in DAX

[Anonymous]

Hi everyone,

I need to display the number of Items with certain filters:

- Item Group is "GLO"

- Items have been purchased within last 12 months at least 10 times ( so, PO number is >= 10 and date filter is in the last 12 months)

I am trying to create a calculated column to see, how many items meet those criterias.

When I put those filters on a table visual, I can clearly see the correct number of items. But I face a problem when I create DAX formula:

Items of interest = CALCULATE(DISTINCTCOUNT('BaseData'[Item]), FILTER(PurchaseOrder, PurchaseOrder[orderdate] >= DATE(YEAR(TODAY())-1,MONTH(TODAY()), DAY(TODAY()))), PurchaseOrder[orderdate] <= TODAY(), FILTER('BaseData', 'BaseData'[ItemGroup] = "GLO"))

Until here the formula work perfectly showing how many GLO items have been purchased within last 12 months.

BUT when I try to add a filter related to number of Purchase Orders, I get a problem. Even after adding 1 more filter, the number doesn't change (meaning I am doing it wrong).

Items of interest = CALCULATE(DISTINCTCOUNT('BaseData'[Item]), FILTER(PurchaseOrder, PurchaseOrder[orderdate] >= DATE(YEAR(TODAY())-1,MONTH(TODAY()), DAY(TODAY()))), PurchaseOrder[orderdate] <= TODAY(), FILTER('BaseData', 'BaseData'[ItemGroup] = "GLO"), FILTER(PurchaseOrder, DISTINCTCOUNT(PurchaseOrder[POnumber]) >= 10)))

Do you know how to solve that problem?

And also, how to make it a True/False slicer? Meaning that I need to apply it a visual (to show visual only for those items of interest).

Thanks in advance!!

[amitchandak]

@Anonymous , With help from date table joined to your date

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-12,MONTH),FILTER('BaseData', 'BaseData'[ItemGroup] = "GLO"))

or

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],today(),-12,MONTH),FILTER('BaseData', 'BaseData'[ItemGroup] = "GLO"))

Then you need a measure

countx(Values([PurchaseOrder[POnumber]), if([ROlling 12] >10, PurchaseOrder[POnumber], blank() ) )

Rolling Months Formula: https://youtu.be/GS5O4G81fww

[Anonymous]

Thanks @amitchandak 

But there is no Sales data in this table or any other tables that I am using for this report.

Is it possible to avoid sales data somehow and receive the same result with number of items?

Thanks!