Isolate Items that are purchased in every month

[Anonymous]

Hello,

I have purchasing data that is added to every month with a monthly time stamp added. In January, all I have is Jan data. In February, I stack Feb under the Jan data, and so forth. I would like to figure out a way that isolates items that are purchased every month. My initial though would be to do a measure that counts each item, and then I can filter to the number of times it shows up (in August, filter to 8). How would this be written in DAX? Is there a better way to do it?

Thanks!

[Greg_Deckler]

Can you provide sample data?

It sounds like you could create a custom column like:

MONTH([Date])

Then you could just do a count aggregation or a measure that uses COUNT or COUNTROWS along with that custom column in the axis.

[Anonymous]

I am a tad confused how I would go about a count aggregation  or a measure using COUNT or COUNTROWS. I'm fairly green with DAX and Power BI, so please bear with me. I have a link to sample data below. Note that items A, C, & D are bought every month, while B & D are not. My goal would be to easily filter out A, C, & D in a report page.

Dropbox

[CheenuSing · Accepted solution]

@Anonymous

Try the following steps

1. Create a measure called NumberofMonthsLapsed

    NumberofMonthsLapsed =  Month(MAx([MonthYear])) - Month(MIn([MonthYear])) + 1 

    This will generate the number of months between the first date and lastdate in the Sheet1 table.

2. Create a summary table  using

    SumCount = SUMMARIZE ( Sheet1,Sheet1[Item],"CountProd",Countrows(Sheet1))

3. Create a column in the SumCount table ShowYes as

   ShowYes = If([CountProd]=[NumberofMonthsLapsed],1,0)

   If the CountProd against each item is same as the NumberofMonthsLapsed set ShowYes as 1 else set it as 0.

4. Create a Table Report with fields from SumCount Table-  Item, CountProd

5. In the VisualFilter Drag the column ShowYes  and set Show items when the value is 1

6. The output will be 

    

If this solves your issue, please accept it as solution and also give KUDOS.

Cheers

CheenuSing

[Anonymous]

That works perfectly. Thank you @CheenuSing