MTD and YTD Pivot table

could you give a sample of data at least to understand what you mean by MDT (presume it is month to date) and YDT (Year to date) you need a place to select a certain date ??? and then these column would display the month to date and year to date ? or ... ???

It would help if you could attach the same file with some sample data showing the output of the pivot.

tks
gowflow

Furthermore in your sample dta you have several rows where you have data category and amounts however the Month and Monthid is BLANK !!! how is that ?

What is the month_id ?? just 1 for January and 2 for February ?

You do not have a date there so we can have you the Month to date.

gowflow

Sorry if I did not explain well.  See the new spread sheet and the sample pivot table.  All I want is to have the Month to date figure based on the Month.  

Thanks
Experts-exchange.xlsx

I don't get it. Although the sample is fine but you replaced Col B Month by Monthend which is fine

HOWEVER
the first few rows shows date 7/31/2013 ... where the fiscal year is 14 !!! and the dates are 2013 is this correct ? or they should be 2014 for fiscal year 14 and 2013 for fiscal year 13 ???

gowflow

Also what about the items that do not have a date ???

By the way would you accept a VBA solution ? as your data is not properly formatted and missing items. You also mentioned in your sample this is for a specific Segment you want to also filter the data by segment ???

so many questions.
gowflow

Every item will have a date.  The fiscal year runs from July of the previous year to June of the current so some date will be 2013 and others will be 2014.  

By the way, I have the pivot table already, if you can create a sumif formula to pick the monthly items for any given month that will be fine.

Thanks

ok so the issue is different now, pls post the pivot and I will work on the rest.
gowflow

Thanks for responding to my posting.  Sorry I did not respond sooner.  See the attached excel spreadsheet.  I have an explanations sheet, that tells the whole story.  Please let me know if you have any questions.  I am also open to suggestions to make this work better.

Thanks once again
2013-and-2014-for-experts-exchan.xlsx

Is this what you want ? I removed the period from general filtering and put it in column filtering this way you have each month separate.
gowflow
2013-and-2014-for-experts-exchan.xlsx

Sorry, this is not what I want.  I want the Month to date column to be in front of the Year to date column as is in the template.  The way I will normally do that in the regular excel spreadsheet is this formula  Month to date:  Current minus Previous.  but I don't know how to do that in the Pivot table.  

I was thinking of using Sumifs but not have no idea that will work.  By the way, I will run the Pivot by Department.

Thanks

ok as not too much familiar with pivots (like you have a field that is %used) and cannot see it in your dataset how did you comeup with this field ?

Could you simply insert the MDT field in the pivot even if it is blank and I will see how to fix your issue? do it and post the new workbook.

gowflow

Oh I see, the %Used field is a calculated field which is created by using a formula.

Thanks

Can you put this formula in the dataset of the worksheet and see if it works?  I tried it, it runs but with large number result.

Thanks

yes mee too.

Question for you
What do you mean by Month to date ???

When you have these values the year to date give you a total for that year of whatever data is there, if you have beside it this Month to date and in the filtering you are asking for All periods then how you expect to have this month to date reflected ?? what do you want to see there ?

I still don't understand what you want to see in month to date. I was able to get the calculated field working but you need to tell me what is this field consist of. Please take a real example and tell me for item so and so I expect to see for 2014 year to date this amount and month to date this amount.


then and only then I can comeup with a formula.
gowflow

The month to date will be amount paid for the given month that you are looking at.  Normally in a excel spreadsheet you will have Current month Year to date data minus Previous month Year to date data.  Thats what month to date is.

Thanks

See the attached spreadsheet.  Its self explanatory.  The Month to date is always changing when you enter a new ytd.  I dont know how to do this in a Pivot table

Thanks
sample-month-to-date.xlsx

yes your sample is fine however the way you have set the pivot it select data for several months unless if u click on period and select 1 month January or February in this case  year to date shows the specific month and not the year to date and this is your problem. I have been trying to explain this to you but you were not grasping it. Hope it is clearer now.
gowflow

Can you give me a better pivot table?  Say with a parameter to pick for each month.

Thanks