cancel
Showing results for
Did you mean:
Member

## Calculate measure based on Filter selection

Dear Community,

Is it possible to create measure to filter the period range to use in visual filter?

Example: In table1,there is a field called SalesArea,period (fiscal Year - which starts from APR to MAR) and Total amount.

Matrix Table Visual:
SalesArea |Period
North | Apr-18, May-18, Jun-18, Aug-18, Jun-19, Dec-19
100,   200
South                               300,      500
East                                                                600, 600

Expected output: For Filter Jun-18

SalesArea |Period
North | Apr-18, May-18, Jun-18
100,   200
South                               300,

Note: year will be not same,but when I select the period from the filter,it has to display from the Starting period to selected period data.

So If I select Jun-19 from the Filter Visual,then I should see the data from Apr-18 to Jun-19 in the table visual.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Member

## Re: Calculate measure based on Filter selection

Hi @Charu ,

Apologies for delayed response.

Use below Measure instead of TotalAmount column.

`AmountDynamic = CALCULATE(SUM(Data[TotalAmount]),FILTER(Data,Data[Date]>=[FiscalStartPeriodLastYear] && Data[Date]<=[SelectedDate]))`

Hope this resolves your challenge if yes, then please mark this as Solution so that others can refer this. Thanks.

8 REPLIES 8
Member

## Re: Calculate measure based on Filter selection

Try this !

Solution

Mark this as Solution if you find this useful.

Regards

nsrshkh

Member

## Re: Calculate measure based on Filter selection

Hi @nsrshkh1 ,

In my data the period will be like the below

YTD-2019,Apr-19,May-19,Jun-19,YTD-2020

So if i select Jun-19 the output should display as YTD-2019,Apr-19,May-19,Jun-19.

I tried the same way of solution that you have shared but in my case filter is not filtering.

Member

## Re: Calculate measure based on Filter selection

Try convert YTD-2019 into a date.

Also mention all scenarios in your query to get a complete picture.

Kindly provide sample data.

Member

## Re: Calculate measure based on Filter selection

Please find the attached sample data and attached the doc file for the expected output.

Member

Try this

Member

## Re: Calculate measure based on Filter selection

This solution is working fine for the table visual but not for matrix visual. Kindly please advise

Highlighted
Member

## Re: Calculate measure based on Filter selection

Hi @Charu ,

Apologies for delayed response.

Use below Measure instead of TotalAmount column.

`AmountDynamic = CALCULATE(SUM(Data[TotalAmount]),FILTER(Data,Data[Date]>=[FiscalStartPeriodLastYear] && Data[Date]<=[SelectedDate]))`

Hope this resolves your challenge if yes, then please mark this as Solution so that others can refer this. Thanks.

Member

## Re: Calculate measure based on Filter selection

Please mark this as Solution so that others can refer this in future.

Thanks

Announcements

#### Community Highlights

Find out what's new in the Power BI Community!

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 378 members 4,008 guests
Recent signins: