Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GarethWoodhouse
Resolver I
Resolver I

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
sturlaws
Resident Rockstar
Resident Rockstar

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
sturlaws
Resident Rockstar
Resident Rockstar

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors