cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dbravo Frequent Visitor
Frequent Visitor

Show data of last months from a particular month filtering with a slicer

Hi,

 

I'm trying to build a report for showing the las 6 months data. Simplifying what we want:

 

I have a table with historical data:

 

data.png

 

I want to show the data like this:

 

Selecting april 2017,

 

apr.png

 

Selecting may 2017,

 

may.png

 

Avg: Average of the last six months

Dev: Relative difference between the month value and Avg, (MonthValue-Avg)/Avg. I cannot get this value in PowerBI, I always get 0% for all months.

 

I've tried using parameters and it works, but in powerbi.com we cannot change them.

 

I hope you can help me, thanks in advance.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Moderator v-caliao-msft
Moderator

Re: Show data of last months from a particular month filtering with a slicer

@dbravo,

 

Please refer to the steps below to achieve your requirement.

  1. Create calculated columns in your original table.
    MonthNumber = SWITCH(Table1[Month],"Jan",1,"Feb",2,"Mar",3,"Apr",4,"May",5,"Jun",6,"Jul",7,"Aug",8,"Sep",9,"Oct",10,"Nov",11,"Dec",12)
    Date = DATE(Table1[Year],Table1[MonthNumber],1)
  2. Create a new table by Enter data
    Capture.PNG
  3. Create acolumn in new created table
    YearMonth = FORMAT(Table2[Date],"YYYY-MMM")
  4. Create a measure in new created table
    SelectMonth = MAX(Table2[Date])
  5. Create a measure in your original table
    DateDifference = 1*(Table2[SelectMonth]-MAX(Table1[Date]))/30
  6. Add this new created measure in your visual filter
    Capture1.PNG

Result
Capture2.PNGCapture3.PNG

 

Regards,

Charlie Liao

View solution in original post

Highlighted
dbravo Frequent Visitor
Frequent Visitor

Re: Show data of last months from a particular month filtering with a slicer


@v-caliao-msft wrote:

@dbravo,

 

Please refer to the steps below to achieve your requirement.

  1. Create calculated columns in your original table.
    MonthNumber = SWITCH(Table1[Month],"Jan",1,"Feb",2,"Mar",3,"Apr",4,"May",5,"Jun",6,"Jul",7,"Aug",8,"Sep",9,"Oct",10,"Nov",11,"Dec",12)
    Date = DATE(Table1[Year],Table1[MonthNumber],1)
  2. Create a new table by Enter data
    Capture.PNG
  3. Create acolumn in new created table
    YearMonth = FORMAT(Table2[Date],"YYYY-MMM")
  4. Create a measure in new created table
    SelectMonth = MAX(Table2[Date])
  5. Create a measure in your original table
    DateDifference = 1*(Table2[SelectMonth]-MAX(Table1[Date]))/30
  6. Add this new created measure in your visual filter
    Capture1.PNG

Result
Capture2.PNGCapture3.PNG

 

Regards,

Charlie Liao


Hi Charlie,

 

Thanks it worked, with some other measures in my report doesn't work, but for my general purposes it's a great solution.

 

And, please could you help me with the measure Relative difference?.

 

Thanks in advance.

View solution in original post

10 REPLIES 10
ibarrau Senior Member
Senior Member

Re: Show data of last months from a particular month filtering with a slicer

Hi, did you try relative date slicer from preview features?

Maybe this post can help:

http://community.powerbi.com/t5/Desktop/Filter-to-select-last-month-and-the-weeks-of-the-last-month/...

 

Regards,

dbravo Frequent Visitor
Frequent Visitor

Re: Show data of last months from a particular month filtering with a slicer

Hi,

 

Thanks for your answer.

 

I'll use the relative date slicer. But I need to calculate the relative difference too and with a measure I always get 0%. Do you know how I can calculate it?

 

Bye.

ibarrau Senior Member
Senior Member

Re: Show data of last months from a particular month filtering with a slicer

Did you check running the values of the divide separated in order to see if the data of each one is correct?

Maybe you just need to get more decimals and the 0 is a rounded value of the result. You can change this on Format tab.

 

Regards,

Moderator v-caliao-msft
Moderator

Re: Show data of last months from a particular month filtering with a slicer

@dbravo,

 

Please refer to the steps below to achieve your requirement.

  1. Create calculated columns in your original table.
    MonthNumber = SWITCH(Table1[Month],"Jan",1,"Feb",2,"Mar",3,"Apr",4,"May",5,"Jun",6,"Jul",7,"Aug",8,"Sep",9,"Oct",10,"Nov",11,"Dec",12)
    Date = DATE(Table1[Year],Table1[MonthNumber],1)
  2. Create a new table by Enter data
    Capture.PNG
  3. Create acolumn in new created table
    YearMonth = FORMAT(Table2[Date],"YYYY-MMM")
  4. Create a measure in new created table
    SelectMonth = MAX(Table2[Date])
  5. Create a measure in your original table
    DateDifference = 1*(Table2[SelectMonth]-MAX(Table1[Date]))/30
  6. Add this new created measure in your visual filter
    Capture1.PNG

Result
Capture2.PNGCapture3.PNG

 

Regards,

Charlie Liao

View solution in original post

Highlighted
dbravo Frequent Visitor
Frequent Visitor

Re: Show data of last months from a particular month filtering with a slicer


@v-caliao-msft wrote:

@dbravo,

 

Please refer to the steps below to achieve your requirement.

  1. Create calculated columns in your original table.
    MonthNumber = SWITCH(Table1[Month],"Jan",1,"Feb",2,"Mar",3,"Apr",4,"May",5,"Jun",6,"Jul",7,"Aug",8,"Sep",9,"Oct",10,"Nov",11,"Dec",12)
    Date = DATE(Table1[Year],Table1[MonthNumber],1)
  2. Create a new table by Enter data
    Capture.PNG
  3. Create acolumn in new created table
    YearMonth = FORMAT(Table2[Date],"YYYY-MMM")
  4. Create a measure in new created table
    SelectMonth = MAX(Table2[Date])
  5. Create a measure in your original table
    DateDifference = 1*(Table2[SelectMonth]-MAX(Table1[Date]))/30
  6. Add this new created measure in your visual filter
    Capture1.PNG

Result
Capture2.PNGCapture3.PNG

 

Regards,

Charlie Liao


Hi Charlie,

 

Thanks it worked, with some other measures in my report doesn't work, but for my general purposes it's a great solution.

 

And, please could you help me with the measure Relative difference?.

 

Thanks in advance.

View solution in original post

Moderator v-caliao-msft
Moderator

Re: Show data of last months from a particular month filtering with a slicer

@dbravo,

 

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

 

Regards,

Charlie Liao

dbravo Frequent Visitor
Frequent Visitor

Re: Show data of last months from a particular month filtering with a slicer

Hi,

 

I've used the same logic for that measure and it worked.

 

I created the measure for calculating the average for only the last 6 months with this formula:

 

Average of last 4 months = CALCULATE(SUM(Data[Value]),FILTER(ALL(Data),and([DateDifference] <= 6,[DateDifference] >= 0)))/CALCULATE(DISTINCTCOUNT(Data[Year Month]),FILTER(all(Data),and([DateDifference] <=6,[DateDifference]>=0)))

Then I created the Relative Difference measure:

 

Relative Difference = ([All types]-[Average of last 4 months])/[Average of last 4 months]

 

* [All Types] is a measure that sums all the values on a month.

 

And it's all. Thank you.

Re: Show data of last months from a particular month filtering with a slicer

Hi dbravo,

 

Thanks for your help on calculating rolling n months.

 

I'm new to powerbi, and need your help.

 

The solution provided by you helps me to calculate rolling n months, but the calendar table I cant use for other calculations.

I need to have a common date filter for all the sheets, and it should work both rolling n months and current month as well.

 

Can you please help me.

 

Regards,

Pavan Vanguri.

pkn Frequent Visitor
Frequent Visitor

Re: Show data of last months from a particular month filtering with a slicer

Hi

As per the solution proposed , I tried using the Datedifference as a measure in the visual but it is not allowing me to filter the measure as it is disbaled 

Do u have any suggestions for it 

Regards
Prabin 

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 275 members 2,448 guests
Please welcome our newest community members: