cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

 

 

 

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

 

 

 

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
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 6 members 3,454 guests
Please welcome our newest community members: