Using FILTER function and CALCULATE filters dont seem to work together

[Anonymous]

Right, my first post so apologies if this has already been covered. I've tried searching and this doesnt appear to have cropped up before.

I am trying to use the CALCULATE function to find TotalQuantity... I use the FILTER function to make sure some values are > 0 etc which all works as expected. However, i then want to make this measure dynamic so you can choose how many months to show e.g. show me the previous 4 months for this measure in chart.

TotalVolume = 

CALCULATE (
                      transaction[TotalQuantity],
FILTER (
              transaction,
              round(transaction[transactionPrice], 2)
                         > round(transaction[catalogueprice], 2)
              && transaction[quantityvalue] > 0
              && transaction[LineUnitPrice] > 0
),
transaction[IsCatalogueItem] = TRUE ()
)

This works, however when i add this as an extra filter ,
DATESINPERIOD ( Date[Date], MAX ( Date[Date] ), - [N Value Value], MONTH )

It doesnt recognise this extra line. However, when i remove the FILTER function from the expression it does recognise it. Which makes me think the FILTER function is making my extra filter in the CALCULATE redundant.

TotalVolume = 

CALCULATE (
                      transaction[TotalQuantity],
transaction[IsCatalogueItem] = TRUE (),
DATESINPERIOD ( Date[Date], MAX ( Date[Date] ), - [N Value Value], MONTH )
)

I have tried a calculated table but again this only shows the selected month in the slicer rather than the selected month + the previous n value.

Any help would be greatly appreciated!

Dave

[tctrout]

I cant help your specific measure, however I believe you are running into nested filter context evaluations.  How it is currently written, it is hard to debug to understnad the inner most filter context.

Instead, I would suggest breaking up your code into variables.  Test each variable as you create them and then crate a final calculate that puts it all back together.  

[Anonymous]

Hi tctrout,

Thanks for your prompt reply. I tried adding the FILTER() section into a variable and then using this variable in the CALCULATE filter however the output remained the same. 

I think your right the variable way is the correct way to go but i just cant find a function or way of bringing this back in without the FILTER function taking precedent and ignoring my dates filter.

Thanks,

Dave

[tctrout]

Try simplifying your dates to help troubleshoot.  My gut feeling, your max isnt working as intended because it is being ignored in another filter context.

Get rid of datesinperiod and the dynamic aspect of it, simply do last month for example.  If that works, start to incorporate the more complex aspects of the measure.

My preference is to avoid built in time intelligence measures, I manually type out the measure how I want it to perform.  It is easier for me to troubleshoot and read.  

the below example does not fit your application exactly, but wanted to provide it to get you thinking of alternative ways to solve the same problem.

Example: (uses end of month to either be start or end of month instead of random day within month)

Dynamic Lookback Measure Example= 

var DynamicVariable = WhatIfScenario[Value] --use what if to set up the dynamic look back

var DynamicPreviousDate = EOMONTH(today(),DynamicVariable)  --to get the end of the month back defined by variable above

var BegRange = EOMONTH(DynamicPreviousDate,-1)+1  --go back a month and +1 day to get start of month
var EndRange = EOMONTH(DynamicPreviousDate,0)  --end of month

var DateFilterRange =

filter(dim_Date

    ,and(dim_Date[Date] >= BegRange

        ,and(dim_Date[Date] <= EndRange

             )

       )

return

Calculate([Your Measure Here],DateFilterRange)

[Anonymous · Accepted solution]

I've gone the lazy route, i dont think this is best practice but i am on a deadline and unfortunately need to get it delivered.

I read your comment above and the dynamic code i had created also uses What-If Analysis so I think keeping the code short and sweet over 2 measures is the best to go until i get time to look further into this.

Created the first Measure:

Total Volume =

CALCULATE (

Transaction[Quantity],

FILTER (

Transaction,

round(Transaction[LUP], 2)

> round(Transaction[CatPrice], 2)

&& Transaction[Quantity] > 0

&& Transaction[LUP] > 0

),

Transaction[IsCatalogueItem] = TRUE ()

)

And then created an aditional measure:

Total Volume by N Months =

CALCULATE(

[Total Volume],

DATESINPERIOD ( InvoiceDate[InvoiceDate], MAX ( InvoiceDate[InvoiceDate] ), - [N Value Value], MONTH )

)

Thanks for your help tctrout you have given me something to think about in the future and hopefully get it into 1 measure instead of 2.

Regards,

Dave