cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

On current selection of filter should show last 12 month data

I have two filter in report, one for fiscal year & other for fiscal month. On current selection of fiscal year & fiscal month, the entire page should last 12 month of data. Suppose if I choose "Jan" as fiscal month & "2017" as fiscal year then it must show previous 12 month of data from fiscal month Jan 2017. Please Guide...

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: On current selection of filter should show last 12 month data

Hi @Anonymous,

 

Based on my test, we can take the following steps to meet your requirement.

 

1. Enter the data as you described, and create a new column.

Yearmonth = YEAR(Table1[date])*100+MONTH(Table1[date])

2. Create a YearMonth table and add an index column

distime = DISTINCT(Table1[Yearmonth])
MonthIndex = 
VAR MonthRow = distime[YearMonth]
RETURN
    CALCULATE (
        DISTINCTCOUNT ( distime[YearMonth] ),
        FILTER ( distime, 'distime'[YearMonth] <= MonthRow )
    )


3. Create the three measures as below.

monthtocheck = CALCULATE(MAX(distime[MonthIndex]),ALLSELECTED('Calendar'[YearMonth]))
SelectMonthIndex = 
    CALCULATE (
        MAX ( distime[MonthIndex] ),
        FILTER (ALL(distime), (SELECTEDVALUE(FY[Year])*100+SELECTEDVALUE('Month'[month]))=distime[YearMonth]))
Previsou 12 = IF(AND([monthtocheck]>[SelectMonthIndex]-12,[monthtocheck]<=[SelectMonthIndex]),1,0)

4. Create a table visual and filter the table making the Previsou 12 is 1. Then we can get the result as below.

 

1.png

 

For more details, please check the pbix as attached.

https://www.dropbox.com/s/y4pcfw8kburinob/On%20curren2.pbix?dl=0

 

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Community Support Team
Community Support Team

Re: On current selection of filter should show last 12 month data

Hi @Anonymous,

 

Based on my test, we can take the following steps to meet your requirement.

 

1. Enter the data as you described, and create a new column.

Yearmonth = YEAR(Table1[date])*100+MONTH(Table1[date])

2. Create a YearMonth table and add an index column

distime = DISTINCT(Table1[Yearmonth])
MonthIndex = 
VAR MonthRow = distime[YearMonth]
RETURN
    CALCULATE (
        DISTINCTCOUNT ( distime[YearMonth] ),
        FILTER ( distime, 'distime'[YearMonth] <= MonthRow )
    )


3. Create the three measures as below.

monthtocheck = CALCULATE(MAX(distime[MonthIndex]),ALLSELECTED('Calendar'[YearMonth]))
SelectMonthIndex = 
    CALCULATE (
        MAX ( distime[MonthIndex] ),
        FILTER (ALL(distime), (SELECTEDVALUE(FY[Year])*100+SELECTEDVALUE('Month'[month]))=distime[YearMonth]))
Previsou 12 = IF(AND([monthtocheck]>[SelectMonthIndex]-12,[monthtocheck]<=[SelectMonthIndex]),1,0)

4. Create a table visual and filter the table making the Previsou 12 is 1. Then we can get the result as below.

 

1.png

 

For more details, please check the pbix as attached.

https://www.dropbox.com/s/y4pcfw8kburinob/On%20curren2.pbix?dl=0

 

Regards,
Frank

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

Re: On current selection of filter should show last 12 month data

Thanks for the solution.

 

I need one more help:

 

1. On another page, I have Fiscal year filter, on a current selection, it must show selection fiscal year data & previous two fiscal years of data.

 

2. On another page, I want a Fiscal quarter filter such that, on a current selection, it must show the selected fiscal quarter as well as previous three-quarters data.

 

I'm sharing you .pbix file:

https://www.dropbox.com/s/any7ernb6sz14x1/Test.pbix?dl=0

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!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 55 members 1,161 guests
Please welcome our newest community members: