Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
atitarev
Helper I
Helper I

Use the FY and Month slicers as the last month, show previous six months

Hello,

 

In the screenshot below is the chart I want to be displayed if I select Financial Year = "2019" and MONTH = "December"

Select FY and one Months show six months.PNG

 

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

Display Last N Months & Selected Month using Single Date Dimension in Power BI – Some Random Thought...

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!

 

1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

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

11.png

 

Best Regards

Maggie

 

 

 

View solution in original post

4 REPLIES 4
v-juanli-msft
Community Support
Community Support

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

11.png

 

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.

ryan_mayu
Super User
Super User

@atitarev

 

You can try below solutions.  Hope this is helpful.

 

1. Use Visual level filters to only filter that chart.

1.JPG

 

2. Break the links with current slicers in the Edit interacitons.

2.JPG

 

3. restrict the time in the measure.

example= calculate(measure, filtering(table, year="2019"&&month="December")

 





Did I answer your question? Mark my post as a solution!

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.