I am new to excel dashboard reporting as I have mostly done reporting on QlikSense which uses set analysis. Since QlikSense is no longer going to be free so thought of learning Excel Power Pivot and DAX formulas as most of my personal requirements can be fulfilled by Power Pivots.
I have been able to import the data from Service Now in Power Pivot with the help from a member in the forum as there were issues with the Region which I was not aware of.
I have been able to create a Bar Chart for Month on Month incident count but unable display only the data for the last 6 moths. I created a calculate column in the Power Pivot which would contain "T" of "F" depending upon the below condition and then filter it but the issue is it takes from today's date which would result in data being lost. Incident 1 is the name of the table and opened_at is the date field.
=if('incident 1'[opened_at] > EDATE('incident 1'[opened_at], - 6) ,"T", "F")
Attached is the sample data:
"number","opened_at","short_description"
"INSFDSFDSFDS","01-10-2019 03:16:10 PM","TEST directory results - network names and logos"
"INSDFDSF","06-24-2019 10:13:18 AM","Issues with enet Administrators)"
"INCSDFDSFDS","07-15-2019 11:36:05 AM","Data Load"
"INDFSGERREE","08-09-2019 04:22:40 PM","Please load file for Data"
Also is there any other way of doing it other than creating a calculated column and then filtering on it.
Thanks
Sunil
(A new Excel Fan)
Hi Sunil,
Why don't you use the Filters to filter the PivotTable/Chart to show the date range you want, rather than writing a complicated measure?
Mynda
BTW, there was no file attached.
Hi Mynda,
It is more of a learning that I wanted to do and also the reason for limiting it to latest 6 months is to just fix it and user doesn't have to change the filter criteria.
Also if there are multiple years then to get the latest 6 months every time filtering would be required. And also would that not make the dashboard cluttered.
Thanks
Sunil
Hello Sunil,
If you only want six months data then it is of course simplest to limit the export from ServiceNow, but it is doable to also filter in Power Query or in Power Pivot. But simplest is sometimes best. If you can provide a sample file showing the data you have and of course what you would like the end result to be, I’m pretty sure you will get some ideas presented on how to work it out.
I can also recommend the Power Query and Power Pivot courses, they are well worth the time and money, taking those have at least helped me a lot.
Br,
Anders
Hi Andres,
Due to the sensitivity of the data I cannot share the exact file but the format would be the same as shared above. Also I cannot limit the data to just 6 months as I am preparing other graphs on the data being pulled and I need to pull all the data for the resources in my team.
Thanks
Sunil
Hi Sunil,
Again, you didn't attach a sample file, so we don't know the format of your data. If you want help, then the quickest way is to take a bit of time to create a mock-up file containing your data. That way there is no ambiguity on our part and we're able to clearly understand what you're working with. Often there is critical information omitted from a written description which results in a lot of wasted time on both sides.
That said, you can use CALCULATE with filter criteria on the date field to restrict data included in your measures, but I wouldn't recommend hard coding filters like this ever. Slicers/Filters are the way to go. You can apply the filter in the PivotTable and your users can't change the filtered period on the charts as long as you don't provide them with a date slicer or the field buttons on the chart.
Mynda
Hi,
Attached is the sample data for reference.
Hi Sunil,
Attached is an example file based on your data. You can use a Timeline Slicer to select the period you want to display. Then hide the slicer on another sheet if you don't want your users to edit the time period displayed in the chart.
Mynda
Hi Mynda,
Thanks for the quick response. One concern that I see is with data increasing over the years the timeline would increase and lot of scrolling would be required. Is there a simpler way of achieving this such as limiting the timeline to a year.
Thanks
Sunil
Hi Sunil,
You can change the view of the timeline to aggregate by year, quarter or month. Click the drop down button beside Month in the timeline.
That said, I would strongly recommend against retaining old data that no one wants to see. It's just going to bloat your file unnecessarily.
Mynda
Hi Mynda,
Attached is a workbook which shows how I tried to achieve it in LastnMonths tab. I am sure you would come up of a better way of doing it but this is how I tried to achieve it.
Also there is another tab which is OpenVsClosed_WeekonWeek. How do I display in the line chart graph only for those values where Diff_Count_Open_Vs_Closed is greater than Zero and not show that column in the line chart.
Also is there a way to truncate and load the Model data ??
Thanks
Sunil
Hi Sunil,
Thanks for sharing your file. I think you'll find the 'open_last_n_months' formula will become slow and unwieldy as your data grows. Right now you only have 300 rows of data so it's not a problem, but this is a very inefficient formula for Power Pivot and eventually it will grind the file to a halt. I wouldn't use it.
I recommended the timeline slicer and I still stand by that as the preferred method with almost zero calculation overhead.
Please start new threads for your other questions as they are on a different topics. BTW, I don't know what you mean by this: "is there a way to truncate and load the Model data ??"
Mynda
