Cumulative Measure Not Accepting A Filter

[BekahLoSurdo]

Hi everyone,

I have a cumulative revenue funnel where each level incorporates all of the values from the levels beneath plus additional values. In order to achieve this, I used the following measure:

Potential Revenue Unweighted = CALCULATE( 
    SUM( 'Revenue Funnel'[Amount] ),
    FILTER( ALLEXCEPT( 'Revenue Funnel', 'Revenue Funnel'[Date] ),
        'Revenue Funnel'[Revenue Hierarchy] >= MIN( 'Revenue Funnel'[Revenue Hierarchy] ) ) )

I need to be able to slice this by date (hence the ALLEXCEPT function) but despite the visual picking up the supposed filter, nothing on the visual changes when various years are selected.

Is there something else I need to specify in my measure? Thank you in advance.

[sturlaws · Accepted solution]

Hi @BekahLoSurdo ,

You have 'Revenue Funnel'[Date] in the ALLEXCEPT-function. Which means any filter on 'Revenue Funnel'[Year] will be removed by the ALLEXCEPT-function. So either replace 'Revenue Funnel'[Date], or add 'Revenue Funnel'[Year], in the ALLEXCEPT-function

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

[BekahLoSurdo · Accepted solution]

Thanks for watching out, @Icey. This didn't immediately solve my issue and I've been playing with it before replying to cover all of my bases.

I appreciate the answer @sturlaws, it really pointed me in the right direction. I think this is what you were talking about but I couldn't quite get it to work:

Potential Revenue Unweighted = CALCULATE( 
    SUM( 'Revenue Funnel'[Amount] ),
    FILTER( ALLEXCEPT( 'Revenue Funnel', 'Revenue Funnel'[Date].[Year] ),
        'Revenue Funnel'[Revenue Hierarchy] >= MIN( 'Revenue Funnel'[Revenue Hierarchy] ) ) )

...however it got me thinking about the date in the filter context. I tried replacing it with another (non-hierarchical) dimension and when I put that dimension on a slicer it worked. So the problem seems to be with how my code is interacting with the default date table. (Hmmm....)

I ended up solving it in a roundabout way by creating a new ("Whole Number") column that parsed out the year from the date column and that worked:

Potential Revenue Unweighted = CALCULATE( 
    SUM( 'Revenue Funnel'[Amount] ),
    FILTER( ALLEXCEPT( 'Revenue Funnel', 'Revenue Funnel'[Parsed Year] ),
        'Revenue Funnel'[Revenue Hierarchy] >= MIN( 'Revenue Funnel'[Revenue Hierarchy] ) ) )

I'll pro-actively mark this as solved due to the workaround but if anyone has any insights into how to actually use the date hierarchy in this instance, I'm all ears!

Thanks all. 

[Icey]

Hi @BekahLoSurdo ,

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards
Icey

[sturlaws · Accepted solution]

Hi @BekahLoSurdo ,

You have 'Revenue Funnel'[Date] in the ALLEXCEPT-function. Which means any filter on 'Revenue Funnel'[Year] will be removed by the ALLEXCEPT-function. So either replace 'Revenue Funnel'[Date], or add 'Revenue Funnel'[Year], in the ALLEXCEPT-function

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.