Complex calculations on a pivot table

Copper Contributor

I have a large database and pivot table, with monthly data. I created a sample database to show you the problem I am having.

The underlying data looks something like this (just clipped a few lines)

 

accountnamemonthsales
1090HertzJan-2113
1090HertzFeb-217
1090HertzMar-218
1090HertzApr-212
1121AvisJan-214
1121AvisFeb-217
1121AvisMar-219

 

The resulting pivot returns this

 

LorneS_0-1634414849911.png

What I want to do is display only the accounts where sales in 2 of the last 3 months are 5 or less.

I tried calculated items - but on my original pivot table this results in an error " too many records" 

I've tried other iterations, none work without a ton of manual intervention.

Can someone point me in the direction of a solution or a hack?

 

1 Reply

@LorneS @Sergei Baklan 

 

Load your data set into the data model, and create a DAX measure.  See tutorial below

 

https://youtu.be/e-CFYi52gpc

 

If you have a DAX measure question, ask it here Mr. Sergei Baklan is the DAX expert in the community