Calculate measure based on Filter selection

[Charu]

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.

[Anonymous]

Try this !

Solution

Mark this as Solution if you find this useful.

Regards

nsrshkh

[Charu]

Hi @Anonymous ,

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.

[Anonymous]

Try convert YTD-2019 into a date.

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

Kindly provide sample data.

[Charu]

Hi @Anonymous 

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

[Anonymous]

Try this

Hope this resolves your challenge

[Charu]

Hi @Anonymous 

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

[Anonymous · Accepted solution]

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.

[Anonymous]

@Charu 

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

Thanks