Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
In the screenshot below is the chart I want to be displayed if I select Financial Year = "2019" and MONTH = "December"
I need to keep the existing slicers, they are heavily used for other components (not shown in the the report linked below) but I might need some invisible slicers.
Some good links on the topic I found, especially the 2nd:
Show last 3 months from selected month in Power BI
I find it a little bit difficult to fit to my example.
Here's the link to the abreviated report with the chart I need to get working - PBIX file
I saved it with the month slicer unselected and the FY slicer currently has 2019.
Your assistance will be appreciated!
Solved! Go to Solution.
Hi @atitarev
a workaround you may try.
1. create a what-if parameter, from 1-20 increated by 1.
this will generate a calculated column and a measure.
add the calculated column "Parameter" in the slicer, enter "6" in this slicer.
2.create a new table with this formula
Table = SUMMARIZE(Dates,Dates[FinancialYear],Dates[Monthnum],Dates[MonthNameShort])
don't connect this table to your other tables.
add "FinancialYear" and "MonthNameShort" in the slicers.
3.create measures in "DATA" table
year_select = SELECTEDVALUE('Table'[FinancialYear]) month_select = SELECTEDVALUE('Table'[Monthnum]) n_months = [month_select]-[Parameter Value] meet condition = IF ( MAX ( Dates[FinancialYear] ) = [year_select] && MAX ( Dates[Monthnum] ) <= [month_select] && MAX ( Dates[Monthnum] ) > [n_months], 1, 0 ) accumulation_sum = CALCULATE ( [LTIFR], FILTER ( ALL ( DATA ), [Calendar Date] <= MAX ( DATA[Calendar Date] ) && [meet condition] = 1 ) ) Or sum_eachrow = CALCULATE([LTIFR],FILTER(DATA,[meet condition]=1))
4. add [meet condition] in the Visual level filter as below
Best Regards
Maggie
Hi @atitarev
a workaround you may try.
1. create a what-if parameter, from 1-20 increated by 1.
this will generate a calculated column and a measure.
add the calculated column "Parameter" in the slicer, enter "6" in this slicer.
2.create a new table with this formula
Table = SUMMARIZE(Dates,Dates[FinancialYear],Dates[Monthnum],Dates[MonthNameShort])
don't connect this table to your other tables.
add "FinancialYear" and "MonthNameShort" in the slicers.
3.create measures in "DATA" table
year_select = SELECTEDVALUE('Table'[FinancialYear]) month_select = SELECTEDVALUE('Table'[Monthnum]) n_months = [month_select]-[Parameter Value] meet condition = IF ( MAX ( Dates[FinancialYear] ) = [year_select] && MAX ( Dates[Monthnum] ) <= [month_select] && MAX ( Dates[Monthnum] ) > [n_months], 1, 0 ) accumulation_sum = CALCULATE ( [LTIFR], FILTER ( ALL ( DATA ), [Calendar Date] <= MAX ( DATA[Calendar Date] ) && [meet condition] = 1 ) ) Or sum_eachrow = CALCULATE([LTIFR],FILTER(DATA,[meet condition]=1))
4. add [meet condition] in the Visual level filter as below
Best Regards
Maggie
Hi @v-juanli-msft,
Thank you, Maggie, this is exactly what I wanted! The actual measure I needed is "sum_each_row" in your report sample.
You can try below solutions. Hope this is helpful.
1. Use Visual level filters to only filter that chart.
2. Break the links with current slicers in the Edit interacitons.
3. restrict the time in the measure.
example= calculate(measure, filtering(table, year="2019"&&month="December")
Proud to be a Super User!
Hello @ryan_mayu,
Sorry, but your suggestion wouldn't work at all.
I thought I explained the requirements but let me know what part is not clear in my original post.
If I select "2019", "December", I expect the chart to display 6 months (ungrouped) including Dec: Jul, Aug, Sep, Oct, Nov and Dec of 2019 (FY, ie. 2018 calendar year in Australia)
If I select "2018", "December", I expect the chart to display 6 months (ungrouped😞 Jul, Aug, Sep, Oct, Nov, Dec of 2018 (FY, ie. 2017 calendar year in Australia).
No hard-coding years and months in measures would work - I can choose any year and month in the slicers and would expect 6 months's worth of data - relative to the selected period. Yes, I would need to edit interactions, if I add a different slicer that works for my requirement that (hidden) slicer would need to be associated with the original one.
The expected behaviour is similar to Display Last N Months & Selected Month using Single Date Dimension in Power BI – Some Random Thought... I provided in my first post.
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |