Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
User | Count |
---|---|
85 | |
76 | |
72 | |
69 | |
56 |
User | Count |
---|---|
104 | |
99 | |
92 | |
78 | |
69 |