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

Show last 13 months based on user single slicer selection

Hi all,

 

I am building a rolling 13 month net revenue chart and I've came across a requirement to show the last 13 month in x axis based in what month (YYYYMM) the user selected in a YYYYMM slicer.

 

The chart below is the one I want:
rolling 13 chart.png


In order to get the intended behaviour, currently I need to select each one of the 13 month in YYYYMM slicer:

rolling 13 slicer.png

 

Any ideas on how I can get this to work in Power BI so that the user just have to select the base month?

Thanks in advance,
Cristhian.

1 ACCEPTED SOLUTION

Accepted Solutions
matemusic Regular Visitor
Regular Visitor

Re: Show last 13 months based on user single slicer selection

Hi again Smiley Happy

 

I understand now, and I tried second approach. I created two tables, which are not related to any of tables. One is Year, with one column (values: 2010, 2011, 2012... 2016). Another is Month, with one column (values: 1,2,3...12).  

 

Then I created this measure:

Sum of profit = 
var Ldate = date(max('Year'[Year]);max('Month'[Month]);1) //Last date

var Fdate = EDATE(Ldate;-13) //First date

var SumProfit = sum('traffic'[profit]) //Calculation

return

 if(min('Date table'[Date])<fdate;
         blank();
         if(min('Date table'[Date])>Ldate;
                   blank();
                   SumProfit))

On x axis I added all months and years (2010-1, 2010-2 .... 2016-12) and in value I added measure.

Show items with no data must be unchecked.

data.pngreport.png

16 REPLIES 16
Super User
Super User

Re: Show last 13 months based on user single slicer selection

Hi There,

 

You can use timeline visuals slicer for your specific requirement. 

This is can be downloaded at 

 

https://app.powerbi.com/visuals/

 

This video blog by amir netz will give you more information on how to leverage that visual for your specific requirement.

 

https://powerbi.microsoft.com/en-us/blog/visual-awesomeness-unlocked-the-timeline-slicer/

 

 

Thanks & Regards,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
cneumann Frequent Visitor
Frequent Visitor

Re: Show last 13 months based on user single slicer selection

Hi Bhavesh,

 

I have already seen this custom visual but it "visually" doesn't fit very well in the reports, since it occupy too much space in the report view.

 

Also, it does not fully cover the requirements since it is necessary to select the range, not just select the base month and the visual slices the base month plus the last 12 months.

 

In the PowerBI Ideas forum, there is a post requesting better date range filters, which I expect to make timeslicer visual more applicable.

 

Thank you for your response,

Cristhian.

v-micsh-msft New Contributor
New Contributor

Re: Show last 13 months based on user single slicer selection

Hi cneumann,

There are already questions asked in the forums here to request the slicers for a special time range.

Well currently I can’t give any DAX resolution regarding how to write the measure to work with a time range, based on a single selection.

Check the similar thread:

http://community.powerbi.com/t5/Desktop/Dynamic-Display-Last-12-Months-in-Visual-According-to-Month/...

By the way, I have voted for Better time range filters.

Regards

 

cneumann Frequent Visitor
Frequent Visitor

Re: Show last 13 months based on user single slicer selection

Hi Michael,

 

Thanks for your reply.

 

Let's wait for the better time range filters arrive. For now, our client will use the YYYYMM slicer and the timeline slicer.

 

-Cristhian.

Super User
Super User

Re: Show last 13 months based on user single slicer selection

Hi Cristian,

 

Can we leverage the power of DAX to deploy your specific requirement.

DAX can polish your measure in a way that whenever end user selects the specific month, it will show last 13 months of rolling revenue.

 

Let me know if I can help you further.

 

 

Thanks & Regards,

Bhavesh

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.
matemusic Regular Visitor
Regular Visitor

Re: Show last 13 months based on user single slicer selection

Hey,

 

if i understand your question right, here is one idea with dax function:

Measure = var LastDate = max(datetable[date])

var FirstDate = date(year(edate(LastDate;-13));month(edate(LastDate;-13));1)

return calculate(yourcalculation;datesbetween(datetable[date];FirstDate;LastDate))

 

Something like this. Let me know if this helps you.

cneumann Frequent Visitor
Frequent Visitor

Re: Show last 13 months based on user single slicer selection

Hi matemusic,

 

I tried your approach but it didn't solve my problem.

 

My measure is already correct, it gives me the rolling 12 month sum based on the X axis month.

 

The point here is that I have to restrict the Date table to show only the 13 months before the selected month. I think that writing a measure for this won't help since I cannot put a measure in the X axis.

 

I've tried the follwing based on your reply:

 

Last13Months = 
VAR 
	LDATE = MAX ( 'msc DimDate'[Date] )
VAR 
	FDATE = DATE ( YEAR ( EDATE ( [LastDate]; -13 ) ); MONTH ( EDATE ( [LastDate] ; -13 ) ); 1 )
RETURN 
	DATESBETWEEN ( 'msc DimDate'[Date]; FDATE; LDATE )

Note that I removed the CALCULATE function since I don't have to calculate a total based on a filter, what I need is to calculte the last 13 months to put this in the X axis.

matemusic Regular Visitor
Regular Visitor

Re: Show last 13 months based on user single slicer selection

Hi again Smiley Happy

 

I understand now, and I tried second approach. I created two tables, which are not related to any of tables. One is Year, with one column (values: 2010, 2011, 2012... 2016). Another is Month, with one column (values: 1,2,3...12).  

 

Then I created this measure:

Sum of profit = 
var Ldate = date(max('Year'[Year]);max('Month'[Month]);1) //Last date

var Fdate = EDATE(Ldate;-13) //First date

var SumProfit = sum('traffic'[profit]) //Calculation

return

 if(min('Date table'[Date])<fdate;
         blank();
         if(min('Date table'[Date])>Ldate;
                   blank();
                   SumProfit))

On x axis I added all months and years (2010-1, 2010-2 .... 2016-12) and in value I added measure.

Show items with no data must be unchecked.

data.pngreport.png

Super User
Super User

Re: Show last 13 months based on user single slicer selection

Hi @matemusic

 

Excellent solution.

 

Cheers

 

CheenuSing

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!