Hi
I have a simple table example below
I am trying to get next months value i.e. for product X in 01/10/2014 I would like to get 20 which is value from 01/11/2014
Can anyone help please?
I tried Calculate(Sum(Product No),NextMonth(Date)) and few other Dax formulas like ClosingBalance etc and nothing works. I am sure I am making some silly mistake here but cannot figure this out.
Product No| Date|Qty
X|01/10/2014|10
Y|01/10/2014|15
Z|01/10/2014|11
X|01/11/2014|20
For a calculated column you could use:
=sumx(filter(Table1,Table1[Product No]=earlier([Product No])&&DATEADD(Table1[Date],-1,month)=earlier([date])),Table1[Qty])
Remember what the dormouse said
Feed your head
Many thanks for your help. I typed exactly as per your post and got an error saying: The second argument of function EARLIER must be an integer greater than zero.
Which version of Excel and PowerPivot? That formula was taken directly from PowerPivot using the sample data you provided, so can you post a sample workbook in which you can't add it?
Hello!
I am using Excel 2013 unfortunately I cannot find infro about version. I believe it is standard that comes with Excel 2013.
I attached an example of spreadsheet last column in red illustrates calculation that I need.
Many thanks for all the help, I really appreciate it.
EDIT:
I got your formula working. It was my fault, typed it incorretly. Unfortunately it does not work the way I would like it and I think I identified the error but have no clue how to fix it
The formula works well in scenario where product ABC is in both months but I may have a scenario where I had no product ABC in April but it appeared in May. In this case I still would like to see May figure as a closing figure for month April as per my attachement
Last edited by illusionek; 01-23-2015 at 08:40 AM.
Any ideas please ?
For your example, do you expect to see rows for both April and May for ABC, or just April?
I would hope to see both as per my attachment. The column in red is what I am trying to achieve.
Many thanks!
I'm not sure if that's possible in Power Pivot as you would effectively be creating rows of data that don't exist.
Many thanks for all your help. Unfortunately I arrived at the same conclusion. The only way I can see it working is if I do cartesian join between months and product numbers in Access for example and only then use your formula.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks