Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dbravo
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
v-caliao-msft
Employee
Employee

@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


@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

11 REPLIES 11
tpchauke
Frequent Visitor

This works perfectly, how do i include same month last year.

v-caliao-msft
Employee
Employee

@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

Anonymous
Not applicable

Hi I downloaded the pbix file and it is working fine for me but when i creating the same in my environment with the same calculations which you used it is not working for me and one more thing i noticed when i selected the report which you created i am not able to see which visualization you used either table format or matrix it is not showing which you used could you please let me know which one you used
Anonymous
Not applicable

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 


@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.

Anonymous
Not applicable

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.

@dbravo,

 

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

 

Regards,

Charlie Liao

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.

ibarrau
Super User
Super User

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,


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

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.

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,


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Happy to help!

LaDataWeb Blog

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.