Month to date calculations based on categories

[kdankwah]

I was able to pull my access data into Powerpivot and been able to run my Pivot table of the data, the problem I am having at the moment is that although my pivot table has YTD figures based on the categories in the dataset, I dont know how to get the MTD figures as well. I have ending dates such 7/31/2013, 8/31/2013, 9/30/2013 etc in the table, I also have the date_id which I created as 1 for July, 2 for August and 3 for Septermber etc. but do not know how to get a formula for this.

Can someone please give me an idea as how to go about getting the month to date figures for say July?

The finished pivot table will look like this

<tbody>
[TD="class: xl69"]Month of September[/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]

[TD="class: xl63, width: 190"][/TD]
[TD="class: xl64, width: 64"]Budget[/TD]
[TD="class: xl64, width: 64"][/TD]
[TD="class: xl64, width: 64"] MTD [/TD]
[TD="class: xl64, width: 64"]YTD[/TD]

[TD="class: xl65"]Office[/TD]
[TD="class: xl66, align: right"]10,373[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]200[/TD]
[TD="class: xl67, align: right"]3,000[/TD]

[TD="class: xl65"]Consultants[/TD]
[TD="class: xl66, align: right"]46,680[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]100[/TD]
[TD="class: xl67, align: right"]4,000[/TD]

[TD="class: xl65"]Telephone[/TD]
[TD="class: xl66, align: right"]17,821[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]500[/TD]
[TD="class: xl67, align: right"]3,200[/TD]

[TD="class: xl65"]Mailing[/TD]
[TD="class: xl66, align: right"]519[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]700[/TD]
[TD="class: xl67, align: right"]700[/TD]

[TD="class: xl65"]Meetings[/TD]
[TD="class: xl66, align: right"]6,224[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]600[/TD]
[TD="class: xl67, align: right"]900[/TD]

[TD="class: xl65"]Travel[/TD]
[TD="class: xl66, align: right"]14,523[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]700[/TD]
[TD="class: xl67, align: right"]3,600[/TD]

[TD="class: xl65"]Training[/TD]
[TD="class: xl66, align: right"]17,820[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]2,300[/TD]
[TD="class: xl67, align: right"]5,000[/TD]

[TD="class: xl65"]Printing[/TD]
[TD="class: xl66, align: right"]0[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]0[/TD]
[TD="class: xl67, align: right"]0[/TD]

[TD="class: xl65"]Publications/Production[/TD]
[TD="class: xl66, align: right"]0[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]0[/TD]
[TD="class: xl67, align: right"]0[/TD]

[TD="class: xl65"]Events[/TD]
[TD="class: xl66, align: right"]0[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]0[/TD]
[TD="class: xl67, align: right"]0[/TD]

[TD="class: xl65"]Computing[/TD]
[TD="class: xl66, align: right"]214,761[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]7,000[/TD]
[TD="class: xl67, align: right"]7,000[/TD]

[TD="class: xl65"]Library[/TD]
[TD="class: xl66, align: right"]519[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66, align: right"]36[/TD]
[TD="class: xl67, align: right"]120[/TD]

[TD="class: xl65"]GrandTotal:[/TD]
[TD="class: xl68, align: right"]329,240[/TD]

[TD="class: xl68, align: right"]12,136[/TD]
[TD="class: xl68, align: right"]27,520
[/TD]

</tbody>

Thanks in advance

 

[scottsen]

Do you have a "real" dates table, such that you can use DATESMTD (and other date-based Dax functions) ?
How did you do YTD? (since I would expect MTD to be fairly similiar) ?

 

[kdankwah]

Thanks for your reply. I have a dates table with a date that is linked to a date in the main table.

The table is like this: the the amounts.

<tbody>
[TD="align: left"]Date[/TD]
[TD="align: left"]Mnthname[/TD]
[TD="width: 64, align: left"]MthID[/TD]
[TD="width: 15"][/TD]
[TD="width: 64, align: left"]Qtr[/TD]
[TD="width: 64, align: left"]Year[/TD]

[TD="class: xl65, align: right"]7/31/2013[/TD]
[TD="align: left"]Jul[/TD]
[TD="align: right"]1[/TD]

[TD="align: left"]Q1[/TD]
[TD="align: right"]2013[/TD]

[TD="class: xl65, align: right"]8/31/2013[/TD]
[TD="align: left"]Aug[/TD]
[TD="align: right"]2[/TD]

[TD="align: left"]Q1[/TD]
[TD="align: right"]2013[/TD]

[TD="class: xl65, align: right"]9/30/2013[/TD]
[TD="align: left"]Sep[/TD]
[TD="align: right"]3[/TD]

[TD="align: left"]Q1[/TD]
[TD="align: right"]2013[/TD]

[TD="class: xl65, align: right"]10/31/2013[/TD]
[TD="align: left"]Oct[/TD]
[TD="align: right"]4[/TD]

[TD="align: left"]Q2[/TD]
[TD="align: right"]2013[/TD]

[TD="class: xl65, align: right"]11/30/2013[/TD]
[TD="align: left"]Nov[/TD]
[TD="align: right"]5[/TD]

[TD="align: left"]Q2[/TD]
[TD="align: right"]2013[/TD]

[TD="class: xl65, align: right"]12/31/2013[/TD]
[TD="align: left"]Dec[/TD]
[TD="align: right"]6[/TD]

[TD="align: left"]Q2[/TD]
[TD="align: right"]2013[/TD]

[TD="class: xl65, align: right"]1/31/2014[/TD]
[TD="align: left"]Jan[/TD]
[TD="align: right"]7[/TD]

[TD="align: left"]Q3[/TD]
[TD="align: right"]2014[/TD]

[TD="class: xl65, align: right"]2/28/2014[/TD]
[TD="align: left"]Feb[/TD]
[TD="align: right"]8[/TD]

[TD="align: left"]Q3[/TD]
[TD="align: right"]2014[/TD]

[TD="class: xl65, align: right"]3/31/2014[/TD]
[TD="align: left"]Mar[/TD]
[TD="align: right"]9[/TD]

[TD="align: left"]Q3[/TD]
[TD="align: right"]2014[/TD]

[TD="class: xl65, align: right"]4/30/2014[/TD]
[TD="align: left"]Apr[/TD]
[TD="align: right"]10[/TD]

[TD="align: left"]Q4[/TD]
[TD="align: right"]2014[/TD]

[TD="class: xl65, align: right"]5/31/2014[/TD]
[TD="align: left"]May[/TD]
[TD="align: right"]11[/TD]

[TD="align: left"]Q4[/TD]
[TD="align: right"]2014[/TD]

[TD="class: xl65, align: right"]6/30/2014[/TD]
[TD="align: left"]Jun[/TD]
[TD="align: right"]12[/TD]

[TD="align: left"]Q4[/TD]
[TD="align: right"]2014[/TD]

[TD="class: xl65"][/TD]

[TD="colspan: 2, align: left"]My years are on Fiscal Year.[/TD]

</tbody>

On the issue of the YTD, I dont have any DAX formula yet except that I just sum the amount, but the problem is how to tell the system to group the MTD based on the Month id. I also have the month id on the master table for each row.

Thanks

 

[kdankwah]

Disregard this typo: "the the amounts".

 

[scottsen]

Assuming the table you put in your most recent response is your date table... that is not a real "date table". I mean, it is a table... and it has dates... but it's not a valid date table by power pivot rules that will let you use all the magic date functions.

Just google for "powerpivot date table" to get some links to what I mean. I thik Rob has some info on it here: The Ultimate Date Table « PowerPivotPro

That is not to say that you MUST have one. I can help you w/ the measures (or calc columns if you really prefer),

You do have data that is daily (or at least... a handful per month), so that MTD makes sense?

You might go check here: Time Patterns – Dax Patterns there is the most typical pattern for MTD/YTD/etc.

 

[kdankwah]

Thanks for all your links and input. I will get back to you after reading. Once again thanks so much.

 

[kdankwah]

I am glad to report that the calendar and MTD measure worked perfectly, by the way the YTD did not seem to work so I used the calculation of SUM(Amount), that worked OK. I will keep prodding the YTD DAX measure and will let you know when I get it to work.

I will email you two sheets named sample and sample 2. My end goal is to recreate Sample 2 using Power-pivot.

The other challenge I am having is how to replicate the green area of the sample sheet. How do you get a subtotal, data and subtotal and then grand total to appear on the sheet. See green area of sample sheet.

Your suggestions will be greatly appreciated.

 

[scottsen]

Hi Kofi,

If we ignore the "green area" which is challenging (it's like a "running sub-total of the stuff above it" ?) your stuff looks relatively "normal". Can you clarify if you have a real date table?

What problem did you have with YTD?