cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
IoanCosmin Regular Visitor
Regular Visitor

Partially override page level filter

I am having challenges getting this measure to work as expected. I have to adjust costs in the current month by calculating the cost in the past months closed and then averaging them.

 

Adjusted costs = 
VAR closedmonth = ClosedMth[ClosedMth Value] -- What-if parameter
VAR selectedyear = MAX(Financials[Year])
VAR selectedmonth = MAX(Financials[Month])
VAR currentmonth = MONTH(NOW())
VAR currentyear = YEAR(NOW())
VAR previousyear = YEAR(NOW())-1
RETURN
    CALCULATE(
        CALCULATE(
            SUM(Financials[Amount]), 
            FILTER(Financials,
                Financials[Account] IN {"50900", "50910"})),
        FILTER(
            ALL(Financials[Month],Financials[Year]),
            Financials[Month]<=closedmonth && Financials[Year]=previousyear))

The measure works properly if I add a slicer for the year and one for the month and have the current month manually selected.

However, I would like to remove those slicer and have that dynamically set so I wrote a calculated column for the current year/month and added it as a page filter. 

 

Current = 
VAR presentMonth = MONTH(NOW())
VAR presentYear = YEAR(NOW())
RETURN
    IF(Financials[Month]=presentMonth && 
       Financials[Year]=presentYear,
        "Current", "Past")    

Unfortunately, once I add this Filter to the Page level filters, the row context overpowers the filter context from the measure. I also tried using a Relative Slicer but couldn't solve the problem. In my case, editing interactions wouldn't work either. I would still want to use a page level filter but somehow have the measure display the correct result.

 

Can someone help me rewrite the measure in order to achieve my goal of only displaying the results for that month by calculating past months costs? How can I get around this problem?

 

Thank you,

C

4 REPLIES 4
Community Support Team
Community Support Team

Re: Partially override page level filter

Hi @IoanCosmin

Do you like this

year month sales measure
2017 9 1 1
2017 10 2 (1+2)/2
2017 11 3 (1+2+3)/3
2017 12 4 (1+2+3+4)/4
2018 1 5 (1+2+3+4+5)/5

 

Best regards

Maggie

IoanCosmin Regular Visitor
Regular Visitor

Re: Partially override page level filter

Thanks for the suggestion, Maggie. To be honest, I don't really like this approach because I would rather stick with a measure than a calculated column. 

 

Is there another way around this?

Community Support Team
Community Support Team

Re: Partially override page level filter

Hi @IoanCosmin

Could you show an screenshot of your expected output?

 

Based on my understanding, you want to remove the slicer where you have the current month manually selected.

then the visual dynamically show the current month's data.

 

If so, you could create a measure to define whether Financials[Month]=Month(now( )) and Financials[Year]=Year(now( )), 

eg. measure=if (max(Financials[Month])=Month(now( )) &&max( Financials[Year])=Year(now( )),1,0)

then add this measure to page level filter and select "show items when values is 1",

 

If i don't understand correctly or my reply doesn't solve your problem, please let me know.

 

Best Regards

Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

IoanCosmin Regular Visitor
Regular Visitor

Re: Partially override page level filter

Hello @v-juanli-msft

 

I am attaching a sample workbook. If you toggle the Page level filter (to Current) you will understand exactly where the problem lies.

When the Page filter is set to (All) the result of the measure is correct. Once you set it to the current month (Current), instead of displaying the same result, it overwrites the logic in the measure. How can I avoid this and still get the correct result with a page level filter?

 

Test Workbook

 

Thank you very much,

Cosmin

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)