Conditional filter based on Dates

[Anonymous]

Hello everyone,

I am thinking about the best way on how to model the following case.

I have a simple table with some Sellers and how much they sold over some months, just like the image below:

Right now I need to filter the sellers and how much they sold by their position/level (jr, mid, senior etc). The biggest challenge here is that some sellers have changed their levels during time, so i have a simple table with just the exact month they have shifted position and the position they have been promoted to. And as a demo, it happens that Seller X has been promoted to Senior in July, so when filtering by 'Senior' i want to show the historic of this specific seller just for the months that he is being working as a Senior, whereas I want to show his historic at the previous position (when filtering it) for the other months. Any tips? 🙂

Thanks in advance!

[Anonymous]

Hello @MFelix, Thanks for replying

It does have more tables. But how can we filter 'Sales'[Sale] or the date field from sales using 'Promotions'[Level]? Looking to the filter flow, it doesn't seem quite clear to me

[MFelix]

Hi @Anonymous,

In this case we are transforming a Many to Many relationship (sales to seller levels) into a many to one - One to many creating the bridge table Seller.

I'm not expert in explaining the full extent of what is happening but please check the video below from Marco Russo where he has several examples.

https://www.youtube.com/watch?v=wRSJ6TYjEu0

If you need anything else please tell me.

[Anonymous]

@MFelix 

Thank you a lot, that really helped me!

[MFelix · Accepted solution]

Hi @Anonymous ,

Try the following:

• Add a end date for you promotion
• Also add a dimension table with the sellers
• Make the following model:

• Add the following measure:

Total Sales =
VAR temptable =
    SUMMARIZE (
        Sales,
        Sales[Month],
        Sellers[Sellers],
        "SAlesTotal", SUM ( Sales[Sales] )
    )
RETURN
    SUMX (
        FILTER (
            temptable,
            Sales[Month] <= MAX ( Promotions[End Date] )
                && Sales[Month] >= MIN ( Promotions[Start Date] )
        ),
        [SAlesTotal]
    )

Check result below and in attach PBIX file:

On the image the Sales is just the simple sum of sales has you can see when I select Senior you only see values on total sales after july.

[Anonymous]

Hello, @MFelix 

I was just playing around with your .pbix file and found something interesting that didn't appear clear to me.

If you could help me understand it, I would really appreciate!

Here is the relationship model:

But how can i manage to filter the view below by 'Level' without setting cross filter to both at the sellers and promotions relationship? Because the way the model is builded, I can't seem to find how the filter flow allows this to happen. I tried this to do the same with my .pbix file where i replicated what was built here but i had to set crossfilter to both. 

Thank you!

[MFelix]

Hi @Anonymous ,

Not sure if I understand your question but based on my setup there is no need to have the cross filter turn on I was abble to do the matrix based on date and Level without any cross filtering.

Is you model only has the one I have send out or do you have more tables?