cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Charu Member
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.

 

Kindly please help me out to achieve with the possibilities.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
nsrshkh1 Member
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
nsrshkh1 Member
Member

Re: Calculate measure based on Filter selection

Try this !

 

Solution

 

Mark this as Solution if you find this useful.

 

Regards

nsrshkh

Charu Member
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.

nsrshkh1 Member
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.

 

Charu Member
Member

Re: Calculate measure based on Filter selection

Hi @nsrshkh1 

 

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

 

Doc file : https://drive.google.com/file/d/1w1Ecxtkj9FGgstWxjlKi4wAmno2Ao3sZ/view?usp=sharing
Power Bi File: https://drive.google.com/file/d/18wBap9yTWLOjmE1YqZ46t79U0shhxgqx/view?usp=sharing

 

 

 

nsrshkh1 Member
Member

Re: Calculate measure based on Filter selection

 

 

Try this

 

Hope this resolves your challenge

Charu Member
Member

Re: Calculate measure based on Filter selection

Hi @nsrshkh1 

 

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

Highlighted
nsrshkh1 Member
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.

nsrshkh1 Member
Member

Re: Calculate measure based on Filter selection

@Charu 

 

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

 

Thanks

Helpful resources

Announcements
Community Highlights

Community Highlights

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

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 378 members 4,008 guests
Please welcome our newest community members: