Adding a FISCAL Year End Date for Running Totals

Gilly Dow
A free video tutorial from Gilly Dow
IT Training Consultant, founder
4.6 instructor rating • 7 courses • 4,415 students

Lecture description

If you require Fiscal year end dates this lecture shows how to utilise the optional filter parameters to get exactly what you need.

Learn more from the full course

DAX Power Pivot Time Intelligence - 10 Easy Steps

Support the Needs of Business Intelligence Using Custom Calendars and Time Period Functions in DAX.

05:48:34 of on-demand video • Updated October 2019

  • Support the needs of Business Intelligence using Time Period Functions in DAX
  • Create running-totals with DATESYTD(), DATESQTD() & DATESMTD()
  • Learn how to use PREVIOUSYEAR() and related DAX functions including PREVIOUSQUARTER(), PREVIOUSMONTH() & PREVIOUSDAY()
  • Customise DAX ready Time Intelligence functions with DATEADD()
  • Learn how to create base measures including FIRSTDATE(), LASTDATE(), ENDOFMONTH() & STARTOFYEAR()
  • Learn how to use CLOSINGBALANCE() family of DAX functions
  • Understand the benefits of FILTER() to super-charge measures in place of CALCULATE()'s "simple" filters
  • Create a 4-4-5 Custom Calendar in Excel and relate to the existing Data Model
  • Learn how to recreate DAX in-built Time Intelligence functions for custom calendars
  • Use DAX Patterns to master navigating the custom calendar
  • Learn how to over-ride the incoming filter context with the ALL() function
  • Suppress totals and subtotals with HASONEVALUE()
  • Create % growth calculations and generate reports to impress
English [Auto] A cable just pick up where we left off. Guys I'm currently on page 14 of these guys and we're going to have a look at the fiscal year. Today it is an optional argument that enables you to customize the can and if you Year-End is done on thirty first of December so we're going to insert into it table into so see 26. So let's zoom over to see 26. And then from the insert type just insert a pivot table and. OK. So it's come into month in the very drop down and then the week number underneath month and it's just total sales. So do a search for total sales. And there it is just there. We're going to add a Canada slice to a calendar year slice so I should say this happens because we've used this search for it to find omega here. Then we can only see the results of our search. So we just have to cancel out of it in order to get all of our tables back from a white year and add a slicer and we're just positioned not just on you it's roughly about I think we're going to add a new measure into our pivot table. So in the sales table and the measure name is total sales for school. And it's going to be the third first of March or in upper case. So again we need our trusty friend our calculate and again the total sales we enter comma and this is our Filcher expression using our IT TAKES here today. All good so far. And again it's from our calendar table it's the date field and this is where we put in the optional parameter. So it's a comma and then it's surrounded by quote marks and it's going to be in the thirty first of the third. And just quote marks. Now we don't need to here. I mean if you add the the function will accept it but it's just ignored and it makes an awful lot of sense if you think about it especially when our year span over several several years if we put a day in there then it would only be subject to that particular year and that's certainly what we don't want so we'll close up with a couple of closing parenthesis. Again it's can't see the zero decimal places. And again we can check up on me now just to make absolutely certain. OK. But it looks good. And again I'm still having this problem and I'll just put in the search for 30 first and it finds it so perfect. And then we'll just drop that undertows with Souths and it is our fiscal year. And now when I'm inserting a fiscal year and normally takes me a while just to make sure that everything is as it should be. First of all I've got to sort out this filter of business so I should say so I equate that to four. And then if we go to 2002 and apply some conditional formatting so I'm going to select some 20 A's and from our hometown we just click on conditional formatting. Select the data box or Archie can choose any one you like us going to choose the blue. And the reason I do this this way is that when the pivot table is sliced the conditional formatting will apply to all the cells. If I highlight a block of cells and apply conditional formatting to the block then it's subject to that range that block that I've highlighted. So if I choose to do it this way. Choose the third option. It says all cells showing told to us with this thirty first of March this week number so does it matter how tall the pivot table is we will also always apply to just that block of cells so choose that third option and conditional formatting makes it an awful lot easier. So is the thirty first of March and this is our greatest value which is the last week in March and the first week starts in April. And we can see our use getting progressively larger and larger so conditional formatting is perfect for this. Now we need to put some formatting on our pivot table. So let's do our text wrap and center both horizontally and vertically will increase the height of the depth of our. See if we can just squish that and bit more. No probably not looking. OK. And I'll show you outline for us here. So we've got a couple of levels in almost drop zone. Now if we go to design and invite say to go in outline form it gives us an extra column because we've got levels underneath of those. So if that suits you that's great. If you prefer not to have it that way then I just add a control set to undo that. And you just have to do this manually. So you could put month and week number if you prefer or just leave it month it's up to you. So outline form is great but it will give you that extra column if you've got levels in your drop zone you both drop so so looking great guys and let's just have another look at that measure. Now I can go into measure's manage Mengesha and get all measure that way or another easy way of doing it looks slightly different but you'll be able to see if I put in 30 first of March here I'm just searching for all measure. You can just right click and it measure OK. So it looks slightly different. There's no coloring on the tax functions or anything. OK so it's calculate total sales dates year to date calendar dates. And that's what we did previously we just stopped it. We didn't add in the optional parameter at the end but here in the fiscal year we just surround our dates with out the year the end in quotes and that's how you apply fiscal energy to your dates to date. Perfect. Now the last thing I want to do here you can see that I've got some subtotals going on and I really don't like subtotals being at the top. So again from the Design Menu subtotals you can choose not to show them at all or you can put them at the bottom and turn them off completely. That's looking great. Okay guys. So that brings us to the end of step one coming up in the practice exercises. You'll have an opportunity to look at the other two members of this family when we're generating money totals. So have a go at month to date in court today. And then in the next video be solutions and we'll go through it together there. I urge you to have a go on your own first apply all the knowledge that you've learned so far and are seeing even the solutions based on guys.