Calculate with a datesbetween and filter combined

[BIDevil]

Hi everybody,

I'm trying to get below query to work.  It's a really simple one: average price at date where the price is not blank.

Pricing Avg At Date  =

CALCULATE((SUM( Fact_Pricing[Growth] ) / DISTINCTCOUNT( Fact_Pricing[D_RetailStore_SKey]) ) ,

        datesbetween( Dim_Date[Date], blank(), max(Dim_Date[Date])),

        FILTER(Fact_Pricing, ISBLANK(Fact_Pricing[Price]) = FALSE)

)

Any ideas?

[tamerj1]

Hi @BIDevil 

please try

Pricing Avg At Date =
AVERAGEX (
SUMMARIZE (
FILTER (
ALLSELECTED ( Fact_Pricing ),
Fact_Pricing[Date] <= MAX ( Dim_Date[Date] )
&& Fact_Pricing[Price] <> BLANK ()
),
Fact_Pricing[D_RetailStore_SKey],
"@Growth", SUM ( Fact_Pricing[Growth] )
),
[@Growth]
)

[BIDevil · Accepted solution]

Hi @tamerj1 , while your answer is heading the right way, it is not the solution. I get the same numbers for all of my customers. But I've been busy myself and found the solution.

While you can only add multiple filters in a calculate, you can only add them on 1 table. It does not work on multiple tables. You then have 2 options.

1. Make an aggregation as @tamerj1 is showing above

2. And this is what I did: just create a new measure based on the previous measure:

//This measure simply filters the prices that are <> 0 (or any other filter to a table you want)

Pricing Avg At Date Step 1 =

CALCULATE((SUM( Fact_Pricing[Growth] ) / DISTINCTCOUNT( Fact_Pricing[D_RetailStore_SKey]) ) ,

        FILTER(Fact_Pricing, ISBLANK(Fact_Pricing[Price]) = FALSE)

)

//Then make the at date measure. If you don't set the blank date to a value, he will pick all the dates and you will get values in not selected months.

Pricing Avg At Date Step 2 =

CALCULATE( [Pricing Avg At Date Step 1] ,

           datesbetween( Dim_Date[Date], blank(), if(ISBLANK(max(Dim_Date[Date])),2000-01-01,max(Dim_Date[Date])))

)

Thank you for your respons @tamerj1 , it made me look further and find my solution.