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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Sweetsoda
New Member

Replacing a Filter Pane filter by a filter in a measure

Hi,

 

I have this measure [xyz] that sums the last 12 month revenue:

 

xyz =
VAR _Cuur_start = Max('Table Calendar'[Date]) -365
VAR _Curr_END = Max('Table Calendar'[Date])
return
    CALCULATE([Total revenue],DATESBETWEEN('Table Calendar'[Date], _Cuur_start,_Curr_END))
 
And the measure [x] should filter [xyz] for a subset of product references (to help forecasting):
 
x =
   calculate([xyz],
    filter('Total Material','Total Material'[Tag]="#Forecast")
     )
 
Yet [x] does not produce the expected results. Please take a look a this picture:
 
Screenshot 2023-02-20 194154.png
The visual is filtered (using the Filter pane) by 'Total Material'[Tag]="#Forecast". I would like to have [x] to have the values of [xyz] in this visual so I can use it without the visual filter. [x] only matches [xyz] for the year total. The references affected by the filter are not sold all months.
 
Any idea to get [x] performing like [xyz] also when the visual filter is cleared?
 
Regards,
2 REPLIES 2
Jngum
New Member

@Sweetsoda 
use this instead 
XYZ=
CALCULATE(
[Total revenue],
DATESINPERIOD('Table Calendar'[Date], MAX('Table Calendar'[Date]), -365, DAY)
)
I Datesinperiod actually returns a table and should give you the desired result.

Hi Jngum,

 

Your solution changes a bit some of the values of [xyz] but doesn't change the behaviour of [x].

 

[x] only matches [xyz] on year subtotals but not at every month level).

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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