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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
BIDevil
New Member

Calculate with a datesbetween and filter combined

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?
 
1 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.

View solution in original post

2 REPLIES 2
tamerj1
Super User
Super User

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]
)

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.

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.