Need to show only last 6 months of data in Visuals

[Anonymous]

Hello All,

I am new to powerbi.

I have created a report and in that I would like to show the visualizations for only last 6 months from current month.

I have muluitple tables in which, each of the table contained Month column. I will be getting data mulitple times for a single month.

So each table for each month contains lots of rows with different values.

Like as        

MIS $ Mln	Year	Month
0.01386	2016-17	February
0.00364	2016-17	February
0.00096	2016-17	February
0.0066	2016-17	February
0.01914	2016-17	February
0.00329	2016-17	February
0.00256	2016-17	January

0.01386	2016-17	January
0.00364	2016-17	January
0.00096	2016-17	January
0.0066	2016-17	January
0.01914	2016-17	March
0.00329	2016-17	March
0.00256	2016-17	March

As i said i have multiple tables like rev, contribution,sales.And in each of tha table,data will be getting as shown above.

So to create a relationship among all these tables with month, I have created another Month table as below and created relashionship with rest of the tables by month Column and it worked.

 Month             No

January	1
February	2
March	3
April	4
May	5
June	6
July	7
August	8
September	9
October	10
November	11
December	12

I have used this table Month column as Slicer and I am able to filter in all the visualizations according to the filteration.

But i dont want to use this slicer furthor.And I did it by unchecking the months from Filters in Fileds but every month i have to manually do this.

How can i achive this.If you need any further information please feel free to ask.

Thanks,

Mohan V

[ceebu]

If you are using the inbuilt data heirarcy, you could probably use the 'Top N' filter ( link here ) to select the latest date and show the last 'X' number of days - However, since you are using an external table to drive Month name / numbers, You could probably create a new column to calculate if the actual date is within the last months or not. For eg:

Is Within Last 6 Mnths= IF(TODAY() - SalesTable[SalesDate] < 180,"Within 6 months","Older")

Then you can use this calcualted column as a Page filter or a Visual Filter to eliminate values older than 6 months.

[Anonymous]

Thanks For your reply @ceebu.

I have tried the below query 

Date Periods = 
VAR Datediff =
    1
        * ( 'Month'[Month Number] - MONTH(TODAY ()) )
RETURN
    SWITCH (
        TRUE(),
        AND ( Datediff <= 0, Datediff >= -180 ), "Last 6 Months",
        Datediff < 180, "Older than 6 Months"
    )

But gives me below output which i am not expecting.

I am expecting the output as

if the current month is April then i would like to show the visuals of 

April, March, February, January,December, November.

Like wise it should change as the month changes like next month is May, then it should show as

May, April, March, February, January,December

Any Suggestions will be appreciable.

Mohan V

[v-caliao-msft]

@Anonymous,

You need to add year to your column.
Year Month = FORMAT(Table2[Date],"YYYY-MMM")
Type = IF(1*(TODAY()-Table2[Date])<180,"Last 6 Months","Older than 6 Months")

Regards,

Charlie Liao

[Anonymous]

Thanks for the reply @v-caliao-msft.

I have used the same query that you have suggested but i'm getting weired output as in below image.Why am i getting this.Im trying to solve this.But i really dont understand why this is happening..

And you have suggested for Year-Month.But we are not using it anywhere, to get output as we required.

What was the need of it?. Jus asking.

Can you please suggest me

[v-caliao-msft]

@Anonymous,

Could you please share you PBIX file, so that we can make further analysis.

Regards,

Charlie Liao

[Anonymous]

Thanks For the reply @ceebu.

I have tried the below by creating calculated column.

Date Periods = 
VAR Datediff =
    1
        * ( 'Month'[No] - MONTH(TODAY()) )
RETURN
    SWITCH (
        TRUE(),
        AND ( Datediff <= 0, Datediff >= -180 ), "Last 6 Months",
        Datediff < 180, "Older than 6 Months"
    )

But it giving me the values of only last 4 months when i click on Last 6 Months  as i have used this column as sclicer.

 Can you please suggest me how can i make it as i want.

And I would like to explain one more time about what i exactly need to get is,

For example:- this is April month then i need to show all the visuals for

LAST SIX MONTHS i.e APRIL, MARCH. FEBRUARY, JANUARY,DECEMBER,NOVEMBER. like that.

Note:-Here DEC and NOV are of previuos year months.

Please suggest me.

Mohan V.