cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
atitarev Regular Visitor
Regular Visitor

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

Accepted Solutions
Community Support Team
Community Support Team

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

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
ryan_mayu Established Member
Established Member

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

@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")

 

atitarev Regular Visitor
Regular Visitor

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

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.

 

Community Support Team
Community Support Team

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

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

atitarev Regular Visitor
Regular Visitor

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

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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 324 members 3,340 guests
Please welcome our newest community members: