Adding a filter from filter pane into a formula

[Anonymous]

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, with no filter selected, it gives me gigantic (wrong) numbers.  

I would like to incorporate this field (seen in the filter area) into my Measure 7 formula, but I've tried adding it as a FILTER in my CALCUATE statement and that does not work.   Not sure what I am doing wrong.   

Tried this, but when I use this it makes the table go blank.  

CALCULATE(SUM(Flu_Snapshots[Actual Value]),FILTER(Flu_Snapshots,Flu_Snapshots[All Revenue Last Period]="Flu All Revenue Last Period"),FILTER(ALL(Dates[Date]), Dates[Date] = _lastDate))*-1

Current formula (shows correct values as long as the filter is manually selected).  

Measure 7 =

VAR _year = SELECTEDVALUE(Dates[Year])

VAR _month = SELECTEDVALUE(Dates[MonthName])

VAR _date = CALCULATE(MIN(Dates[Date]), FILTER(ALL(Dates), Dates[Year] = _year && Dates[MonthName] = _month))

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

RETURN

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

[v-deddai1-msft]

Hi @Anonymous ,

Would you please inform us more detailed information(such as your expected output and your sample data(by OneDrive for Business)) if possible? Then we will help you more correctly.

As far as the measure you gave us, I suggest you change the Measure7 to:

Measure 7 =

VAR _date = MIN(Dates[Date])

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

RETURN

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

Please do mask sensitive data before uploading.

Thanks for your understanding and support.

Best Regards,

Dedmon Dai

[Anonymous]

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]

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

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490