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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
atowriss1
Advocate I
Advocate I

Filter Issue- Filter not working

Hi,

 

I am trying to get # of stores in our pipleline for the next month that have an estimated monthly revenue of 2K or greater.

 

Below is the Dax:

 

test = VAR _medate=selectedvalue(dimDate[Date].[Date])
VAR _numberofmonth=1
return
calculate(sum('EC Closed Report'[# of Rooftops]),
USERELATIONSHIP('EC Closed Report'[Exp Revenue Month Date],dimDate[Date]),

DATEADD(dimDate[Date].[Date],+1,MONTH),
FILTER(
   'EC Closed Report',
   calculate([Closed Rev]>=2000))
)
 
Without the filter, it calculates the correct # of stores, but once I add the filter, it comes up with nothing.
 
I even tried this- but it also retuned a blank:
test = VAR _medate=selectedvalue(dimDate[Date].[Date])
VAr _numberofmonth=1
return
calculate(sum('EC Closed Report'[# of Rooftops]),
USERELATIONSHIP('EC Closed Report'[Exp Revenue Month Date],dimDate[Date]),

DATEADD(dimDate[Date].[Date],+1,MONTH),
FILTER(
   'EC Closed Report',
  ( 'EC Closed Report'[Monthly Rev]>=2000))
)
 
Any ideas on what the issue could be?
 
Thanks
3 REPLIES 3
sevenhills
Super User
Super User

 

 

Try 1:

Test = 
VAR _medate = selectedvalue(dimDate[Date]) -- Not sure what is the use of this one, as no further reference
VAR _numberofmonth = 1 -- Not sure what is the use of this one, as no further reference
RETURN 
     CALCULATE ( 
         SUM ('EC Closed Report'[# of Rooftops]),
         USERELATIONSHIP('EC Closed Report'[Exp Revenue Month Date], dimDate[Date]),
         FILTER( ALL('EC Closed Report'), [Closed Rev] >= 2000)
         --,
         -- Not sure what this is doing here?
         -- DATEADD(dimDate[Date].[Date],+1,MONTH)
)

 

 

 

See if this portion of DAX works ... 

Without the date add it works, but returns stores with expected revenue in November, and I need expected revenue for December, which is why I had the DateAdd.  I tried putting the DateAdd after the filter, but that didn't work either.

-- Try 2: 
Test =  
VAR _medate = selectedvalue('EC Closed Report'[Exp Revenue Month Date]) 
RETURN 
     CALCULATE ( 
         SUM ('EC Closed Report'[# of Rooftops]),
         USERELATIONSHIP('EC Closed Report'[Exp Revenue Month Date], dimDate[Date]),
         FILTER( ALL('EC Closed Report'), [Closed Rev] >= 2000),
         DATESMTD(DATEADD(dimDate[Date], 1, MONTH))  
)

 

Optional Try 2.a.: To see the date range you wanted: 

Period Dates =
-- FYI, this is more for testing, not for calculating your measure
-- Create a table visual with 'EC Closed Report'[Exp Revenue Month Date] and Period Dates Measure and check the values

VAR _medate = selectedvalue('EC Closed Report'[Exp Revenue Month Date]) 
VAR _numberofmonth = 1  
var _nextmonth = IF ( _selDate = Blank(), 
                  EOMONTH(Minx(all('EC Closed Report'[Exp Revenue Month Date]), 'EC Closed Report'[Exp Revenue Month Date]), 1),
                  EOMONTH(_selDate, 1)
                 )
var _nextmonth_StartDate =  Date(Year(_nextmonth), Month(_nextmonth), 1)
VAR _nextmonth_EndDate =  _nextmonth
 
RETURN _nextmonth_StartDate & " : " & _nextmonth_EndDate
 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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