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
cneumann
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

Hi again 🙂

 

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

View solution in original post

22 REPLIES 22
Ydiss
Regular Visitor

Hi @cneumann 

 

I had a similar challenge today and although you've already gotten an answer for this and it has been well accepted, it didn't really seem to work for me and my requirements; I'm not exactly sure why but the syntax just wasn't accepted my end, it didn't like referencing the Ldate in the next var amongst other issues and, even if I could have debugged it and gotten it to work, I was hoping for a slightly more elegant solution that didn't involve creating tables and hard-coding my existing measures.

 

So, I had to muddle through (I'm still really new, only started learning PowerBI and DAX in the last month) and have spent all day on a solution that works for me and I wanted to share it in case anyone else might find it useful for solving this problem. It's a single Measure that can be used against any date field:

 

Within13MonthRange_Date = IF (
    DATEDIFF(
        MAX('Table'[Date]),
	    CALCULATE(
		    MAX('Table'[Date]),
		    ALLSELECTED()),
        MONTH) <= 13,
    1,
    0)

This returns 1 or 0.

 

Replace 'Table'[Date] with whatever date you are slicing on (in your case whatever date is on your revenue table) and it'll return 1 whenever the data in the visual has a date filter context within 13 months of the maximum date you've selected on your slicer OR, if you've not selected anything, the maximum 13 months of your entire data set.

 

You then filter your visual on the measure: is 1 and is not 0.

 

I used this specifically to limit tooltip visuals dynamically to the most recent 12-13 months' data, depending on slicers affecting the page; without this, when you have nothing sliced, the tooltip visual (for example with bar charts with months along the X axis) can show too much data and will be cropped to the first 12-13 months of data, which isn't useful. Using the above measure applied as a filter to the tooltip visual, the visual always represents the last 12-13 months data up until the maximum date sliced, so always shows relevant data dynamically. You can change it to whatever you like (days, months, years and any value) all within a single DAX expression.

 

The result is I went from this:

A tooltip that shows data from May 2017 to April 2018 but with no way to use the slider to view any other data. Not great.A tooltip that shows data from May 2017 to April 2018 but with no way to use the slider to view any other data. Not great.

To this:


Much more useful, always showing the last 12 months data unless the user slices (in which case it'll show the maximum 12 months of that sliced data)Much more useful, always showing the last 12 months data unless the user slices (in which case it'll show the maximum 12 months of that sliced data)

 

 

 

 

 

 

 

 

Hope this helps anyone.

 

 

Hello @Ydiss

 

I've been using BI for about two weeks, so bare with me.  Could you explain how I can put this slicer to work.  It's just what I need for my report but I can't figure out is how to implement it.

 

Thanks in advance

My problem Is how to fix the selector from a period slicer ( example Year-Month) on each row changes, REgards.

matemusic
Advocate III
Advocate III

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.

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.

where does the  [Lastdate] comes from?

What is the Year Month calculation?

Hi again 🙂

 

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

This was a great solution, I had a slightly different measure for distinct count, but overall a perfect fit for what I was lookng to do.

Hey, do you know if there is a way to do this with just ONE slicer?

I am doing something similar though I am only using one slicer to show MMM YY so want to see if it can do the same thing

Hi @matemusic ,

 

just wanted to let you know that your solution helped me to achieve the requirement of my end user. 

 

Kudos to you!!

 

Anonymous
Not applicable

Hello,

 

I found this very helpful. now can you please help me how to show the selected month value in card visual.(without connecting year table and date table)? i use yyyymmm slicer... am using year and month from one table itself

 

Hey @matemusic

 

You have really an excellent solution.

I need to have 2 tables:

1. First table: Should show exactly what you created (it works).

2. Second table: If month selected then it should only show that month. If month is not selected then it should show all dates for that year.

 

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

var Fdate = EDATE(Ldate;0) //First date

var SumProfit = sum(traffic[Amount]) //Calculation

return

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

 

I get right with the month part, but I cannot find out how I can find out the year part. Do you have any ideas?
Here is the file
 

Hi, maybe this will help. I added new coumn Year in the calendar table. Bold blue text in the code represent new code

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

var Fdate = EDATE(Ldate;0) //First date
VAR SYear = CALCULATE(max('Year'[Year]);ALLSELECTED()) -- it can be without calculate and allselected, i am just used to work in that way
VAR SumProfitYear = CALCULATE([profit];'Date table'[Year]=SYear)
var SumProfit = sum(traffic[Amount]) //Calculation
return
 IF(HASONEVALUE('Month'[Month]);
     if(min('Date table'[Date])<fdate;
         blank();
         if(min('Date table'[Date])>Ldate;
                   blank();
                   SumProfit));
                   SumProfitYear)
Anonymous
Not applicable

Thanks for the solution!

I tried to apply your solution, but with 1 slicer that has the Month+Year combined. It did not work for some reason. 

 

I still got it working tough by creating 2 tables

Table1:

Month  - "01"

Year      - "2017"

MonthYear - "201701"

 

Table2:

Month - "01"

 

MonthYearRelation.png

 

Now I can use the YearMonth column in my slicer and your solutions still does the rest 🙂

 

Hello @matemusic,

 

Great solution.

 

Thanks!

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!
BhaveshPatel
Community Champion
Community Champion

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.

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.

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

 

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.