This topic contains 10 replies, has 2 voices, and was last updated by 
-
Posts
-
I am developing a dashboard that provides account balances for various month end dates going back several years. The account balances are also segmented by various categories. What I am attempting is to provide % growth (measure) per category (column header) based on the months selected (row header) by the user via a timeline slicer. My first step was to create the following measure to identify the earliest date per the timeline selection. Here is that measure: [First Date Range]=calculate(MIN(tblcommercialtrending[File Date]),firstdate(tblcommercialtrending[File Date])) I pulled this measure by itself into a pivot table to test and did confirm it syncs up with various timeline selections. My next step was to incorporate [First Date Range] into a separate measure which would sum the account balances by the date provided in the so that I could then calculate the delta between each proceeding month within the range of dates selcected vs. the first month, i.e. [First Date Range]. This second measure is where I am running into issues. Here is the measure
[Balance on First Date]=calculate([Balance],datesbetween(all(tblcommercialtrending[File Date]),[First Date in Range],[First Date in Range]))
where [Balance]=sum(tblcommercialtrending[Note Balance])
When I bring this measure into my pivot table I get the following error: “Data Model could not be accessed. MDX Script Model (6,103) Datesbetween and Datesinperiod functions are only accepting date column references as first argument”.
I feel like I’m close, just can’t figure out the correct method. Any help is greatly appreciated.
Hello sans holo,
First, the error message is telling you to remove the “all” around the first argument in DATESBETWEEN.
Also, without seeing your model, I think that you need a calendar table with a relationship between tblCommercialTrending[File Date] and Calendar[Date]. After creating a calendar table, I would consider reformulating [First Date Range] to
First Date Range:=FIRSTNONBLANK ( Calendar[Date], [Balance] )
and
Balance on First Date := CALCULATE ( [Balance], FILTER ( Calendar, [Date] = [First Date Range] ) )
Thanks Tom. There is a calendar table and my first run at this included FIRSTNONBLANK() on my Calendar[Date] field however I don’t believe my final measure was the equivalent of what you have here. Will give it a try and let you know the results.
OK, I tried it and realized that I have done something similar to what you recommended here with the same result as provided in the attached workbook. I have changed the numbers presented below to protect company info but the issue is still evident which is that [Balance on First Date] is just returning the same number as the [Balance] measure by month. I have attached and example of the results as well as two examples of desired result.
Please evaluate attached workbook.
Workbook has a Power Query to create calendar table. If you open Advanced Editor in Power Query’s Query Editor you will see where to change the date range. This is currently my standard for creating calendar tables, based on a post by Kasper de Jonge. One important change from the original is how the last date of the calendar is calculated.
Added Year-Date calculated column to Calendar, which uses an existing Year Date (no hyphen) column for sort order.
The ALLSELECTED function is the key to making the pivot work.
That did it! I was close and actually looked into both FIRSTNONBLANK and ALLSELECTED as possibilities but could never work out the right combination. This is a powerful combo and really allows users to customize the date frame while recalibrating the starting point for % delta analysis. Will now drop various categories on column axis which makes this tremedously powerful…along with other slicers.
On a side note, do you know of any other Power Pivot users that are in Banking? While the Powerpivotpro blog and print materials are very helpful it would be interesting to talk with other users in the Banking industry who are using Power Pivot. Most of the examples found in print and online are related to sales related data and Banking is of course more of a date and balance type and category data set. Of course with its very own language as well. Just curious, thanks again for your help!
Hello,
Glad to hear of your success!
Regarding the side note, why not start a new post with a topic and post similar to the following:
—-
Are You a Power Pivot User In Banking?I want to connect with other Power Pivot users in the Banking industry.
While Most of the examples found in print and online are related to sales, our (banking) business models are more date, category (account) and balance centric than sales. And of course we have our own language as well…
It would be awesome if we could use this forum to leverage our Power Pivot experience.
—-
Also, I can look out for participants who identify themselves as “in the banking industry”. If a participant does mention that they are in banking, I can come back to this post and leave you a comment that there is a conversation you may wish to join.Let me know what you want to do.
Good idea, will start a new string. FYI, sent you a LinkedIn invite such that you can feel free to refer any PP users in banking industry that want to connect. Thanks again.
Found a bug…in my version. Tested your version by changing moving slicer dates all around and it worked flawlessly. My side works fine except for certain instances where it wants to grab a random date in the list and call it the first date. FYI, my date table is sorted by column by a year date field. I thought that might be it, but doesn’t appear to be so. One example of the issue is if I grab April 2014 (which is the actual first date where my data exists) through September 2014 it works fine. If I slim the date range to May 2014 through September 2014 it thinks that August 2014 is the firstnonblank and grabs the balance associated with that date. Any combination of dates that exclude April 2014 and include August 2014 will show August 2014 as the first nonblank. This is also the case for December 2014 if I exclude August 2014 and include any range of dates to include December 2014, I get that date as the firstnonblank. Very strange. I know it’s probably difficult to decipher without seeing the workbook, any ideas?
Hello,
The sort on the table is probably not the issue, it is the sort of the column.
Some things to check out:
1) Is your dates table marked as a date table?
2) This description reads like you need either to re-evaluate the calculation for the sort-by column used for “Year-Month” or add a “sort by” column to support year-month.
IF you do not have a sort-by column on Year-Month the sort order will be:
2005-Apr
2015-Aug
2015-Feb
2015-Jan
2015-Jul
2015-Jun
2015-Mar
2005-May
2015-Oct
2015-Nov
2015-Sep
2015-Dec3. If the sort-by column for “Year-Month” is calculated similar to: = YEAR ( Calendar[Date] ) & MONTH( Calendar[Date] ), you should reformat it to
= YEAR ( Calendar[Date] ) & FORMAT ( MONTH( Calendar[Date] ), “00” )4. In my example pivot, although I used Calendar[Year-Month] for the Rows drop zone which follows this pattern for April 2015: 2015-Apr, I used Calendar[Year Month] in the calculations, which follows this pattern: 2015-04.
Let me know if any of these suggestions help (they should, but maybe something else is going on).
#3 was the culprit. Constructed the “Year-Month” column in a manner that inadvertnently forced a sort similar to the order you described in #2. I’m up and running, thanks again Tom!
The forum ‘Power Pivot’ is closed to new topics and replies.