Include only articles that have moved consecutively during selected months

[dpollozhani]

Hi everyone,

This is my first post here, as I have only recently started working with PBI (I'm coming from a Qlik world, but that might change). With that in mind, I hope that I'm able to formulate my needs as clear as possible.

So, I have a problem that I've been stuck for quite some while now. 

Intro

I want to analyze the average sales price development on a monthly basis. The requirements are 1) Calculate the average price per company, article, and year-month; then sum these values up (it's specific to our situation); 2) Only analyze articles that have moved consecutively during the time period that is selected by the user, i.e. if I select year 2021 and months jan-sept, I need to dynamically include only articles that have moved at least once per month during 2021 jan-sept.

Month measures

For this I first deciced to calculate how many months are available in total (with a minor tweak to remove the current month under certain conditions), a number that should depend only on the time selection:

Then, I calculate the months that are available, accepting all filters:

Lastly, I create a new measure that is a straight comparison between above two measure, i.e. a boolean that I mean to use as a filter later:

So far so good, in as much as these measures verifiably work. 

Article filter

Now comes the difficult part: creating a filter of articles where this latter condition is true. I have tried many things that don't work, so this is what I have at the moment:

This works - however, it is extremely slow. As you may have already realized, the FILTER(...) with my boolean filter condition is the culprit. The measure takes about 15-20 seconds to calculate upon each new selection. The datamodel is neither complex nor large (I'm working with a tiny subset of the real data). 

Note that the TREATAS comes from the fact that I have a  relationship (many:many, if that matters) between my Fact table and a another table ("ASP") where all prices are already calculated per article, company and year-month (it's not connected to the calendar, hence TREATAS). The thinking behing this was simply to avoid summarizing too much on the fly.

Data model

My questions are: 1) Has anyone ever done this specific kind of analysis; 2) Is there anything you see directly here that should change for a better performance?

I will also gladly accept any improvement suggestions.

Thanks in advance!

[Icey · Accepted solution]

Hi @dpollozhani ,

Try to use the measure below as a filter:

Count =
COUNTROWS (
    EXCEPT ( VALUES ( 'Calendar'[Year Month] ), VALUES ( 'Sales'[Year Month] ) )
) = 0

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dpollozhani]

This does work much, much better, thank you. Do you have an idea of what the reason for the improvement is?