Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Adding a filter from filter pane into a formula

I have the following measure (below).     If I apply a filter context (using the filter pane), the values in my measures get filtered down to the proper amount.    But note that in the 3rd table, wit...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

I will try to be even more specific to help get my post answered.   (Dedmon - thanks for your formula...it was simpler but still provided the same result as my Measure 7.   I called yours Measure 20 in my examples below).  

 

The table on the top left contains correct values.  You can see I have selected a filter (Flu All Revenue Last Period).   If I do NOT select this filter, I get the very large number shown in the table on the right, which is incorrect.  

 

My desired outcome is to incorporate this filter into my formula, rather than have to select it from the filter pane. 

 

 

 

 

 

v-deddai1-msft's avatar
v-deddai1-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ,

 

As I mentioned above, Because we do not know your data structure, it is difficult for us to help you effectively. We can only provide opinions and cannot give accurate answers  without test.

 

I suggest you try :

 

Measure 7 =

VAR _date = CALCULATE(MIN(Dates[Date]), ALL(Dates))

VAR _lastDate = EOMONTH(_date,+0)+28

RETURN

CALCULATE(SUM(Flu_Snapshots[Actual Value]),FILTER(ALL(Dates[Date]), Dates[Date] = _lastDate),

FILTER(ALL('Flu_Snapshots'),Flu_Snapshots[All Revenue Last Period]="Flu All Revenue Last Period"))*-1

 

Best Regards,

Dedmon Dai