Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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.
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
@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
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
@Anonymous,
Could you please share you PBIX file, so that we can make further analysis.
Regards,
Charlie Liao
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.
User | Count |
---|---|
88 | |
73 | |
69 | |
64 | |
56 |
User | Count |
---|---|
98 | |
92 | |
84 | |
74 | |
66 |