Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
Try convert YTD-2019 into a date.
Also mention all scenarios in your query to get a complete picture.
Kindly provide sample data.
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
Hi @Anonymous
This solution is working fine for the table visual but not for matrix visual. Kindly please advise
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |