cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
GarethWoodhouse Regular Visitor
Regular Visitor

Filter not working as expected

 

Hi all. Iniatially posted this in the Dsktop section but wonder if it better fits here

 

I am trying to caculate the variance period on period per customer.

My Period value is in table date

My Customer value is in table customer

My Value is in table Sales Customer Period Value

customer   Period   Value

A                1            100

A                2             200

A                3             100

B                1              300

B                2              250

B                3              200

 

This Measure works as expected

Value1 = VAR periodvalue = value(SELECTEDVALUE('Date LookUp'[Financial Period])) Return Calculate('Sales Invoice History'[Gross Profit Post BDM], FILTER('Date LookUp','Date LookUp'[Financial Period] = periodvalue))

 

However This Measure returns blank

 

Value2 = VAR periodvalue = value(SELECTEDVALUE('Date LookUp'[Financial Period])) Return calculate(sum('Sales Invoice History'[STG_Gross_Profit_Less_BDM]), FILTER('Date LookUp','Date LookUp'[Financial Period] = periodvalue +1))

 

periodvalue + 1 is calulcating correctly and giving me the expected outcome so it's not that

If I take periodvalue + 1 and enter a hardcoded value I still get blank.

 

What have I done wrong here in my filter?

 

My plan was to do Value1 - Value2 to get the variance and drop the measure into my table to calculate by Customer

If anyone can help or shed some lighton where I am going wrong it would be appreciated.

 

Thanks.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Filter not working as expected

hi, @GarethWoodhouse ,

 

could you try to wrap an ALL() around 'Date Lookup' inside the filter, like this:

Value2 =
VAR periodvalue =
    VALUE ( SELECTEDVALUE ( 'Date LookUp'[Financial Period] ) )
RETURN
    CALCULATE (
        SUM ( 'Sales Invoice History'[STG_Gross_Profit_Less_BDM] ),
        FILTER (
            ALL ( 'Date LookUp' ),
            'Date LookUp'[Financial Period] = periodvalue + 1
        )
    )

 

Cheers,
Sturla

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

View solution in original post

2 REPLIES 2
Super User III
Super User III

Re: Filter not working as expected

hi, @GarethWoodhouse ,

 

could you try to wrap an ALL() around 'Date Lookup' inside the filter, like this:

Value2 =
VAR periodvalue =
    VALUE ( SELECTEDVALUE ( 'Date LookUp'[Financial Period] ) )
RETURN
    CALCULATE (
        SUM ( 'Sales Invoice History'[STG_Gross_Profit_Less_BDM] ),
        FILTER (
            ALL ( 'Date LookUp' ),
            'Date LookUp'[Financial Period] = periodvalue + 1
        )
    )

 

Cheers,
Sturla

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

View solution in original post

GarethWoodhouse Regular Visitor
Regular Visitor

Re: Filter not working as expected

Initially this didn't work for me but I then realised it was because I also had a Year field and the All on the filter was bypassing this page filter. By adding a filter on selected year into the value2 code I got this working perfectly. Thanks all for your help. For anyone else experiencing similar problems here is the final code that works for my exmple

GP Variance =

VAR periodvalue1 = VALUE ( SELECTEDVALUE ( 'Date LookUp'[Financial Period] ) )

VAR Value1 = CALCULATE ( sum('Sales Invoice History'[STG_Gross_Profit_Less_BDM]), FILTER ( 'Date LookUp', 'Date LookUp'[Financial Period] = periodvalue1 ) )

 

VAR periodvalue2 = VALUE ( SELECTEDVALUE ( 'Date LookUp'[Financial Period] ) -1 )

VAR yearvalue2 = VALUE ( SELECTEDVALUE ( 'Date LookUp'[Financial Year] ) )

 

Var Value2 = CALCULATE ( sum('Sales Invoice History'[STG_Gross_Profit_Less_BDM]), FILTER ( all('Date LookUp'), 'Date LookUp'[Financial Period] = periodvalue2 && 'Date LookUp'[Financial Year] = yearvalue2 ) )

 

Return IF(Value2 <> 0,Value1 - Value2,0)

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors