Calculate monthly amount based off yearly data

[dadadadeerz]

I have a client with a large data set of yearly financial info. I would like to be able to calculate the monthly amount (MTD) based on the year-to-date (YTD) data set only, rather than have two different sets of data (one for the P&L, and one for the Balance sheet). I have struggled to get something to work. The output needs to be in Excel (either as a table or as a pivot table) to create their monthly reports. 

So far in PowerQuery (PQ), I have taken the YTD data, used a period table to reference the previous period (off calendar year end), and used a conditional column to calculate the MTD amount - it generally works except that a) it's not calculating a zero value difference when certain attributes (company code) have changed from one month to the next [see attached], and b) the PQ has a significant refresh lag. The company has a large (+35k) chart of accounts, and I've used this as a lookup table to try to resolve this issue w/ no luck. 

Here is what the YTD values are for Sep and Oct, with a calc'd MTD of Oct of $219,732.47 Here is what the MTD values are based on my existing model.

Based on my research, I can't find any specific DAX formula that will disaggregate info on a YTD basis down to a MTD basis. I thought that if there was a way to force values that are null in a period (company 160 in 09-2018, for example), so that when I go to calculate the MTD difference in PQ, it will calculate the expected $219k. 

Thanks

[dax]

Hi dadadaeerz, 

Did you have date column in your dataset? As I know, we could calculate YTD based on correconding month. But you want to calculate MTD based on year, I can't reproduce your requirement based on your description, so if possible, could you please inform me in details(such as your data sample)?

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dadadadeerz]

Thanks . I don't have the actual date for each month included in the dataset, although I could transpose it to be included. I am currently using a MM-YYYY field. The year-end is currently October 31st, but will soon be changing to December 31st, and so if I did transpose it, I would use a table so that I could later change it. I do have a table that I am using to calculate the previous month's period value - I have a fiscal year column that differs than the calendar year value, and could add a fiscal year month if needed.

Here is the period table:

Thanks!