GUYS PLEASE HELP ME!! (Cumulative P&L each month but want a pivot with both MTD and YTD data)

[ScopseG]

Hi everyone,

Hope everyone is well.

I am fairly new to Powerpivot. I am stuck on something which is driving me nuts and hope someone can help. I have P&L data which is cumulative only (Data for this specific excercise can only be obtained cumulatively). I want to be able to show the data in a P&L format like so( MTD and YTD) in the columns.

How can one do this is im working with only cumulative data.i.e. I have August data but its YTD, July data buts its YTD. August YTD minus July YTS will give me August MTD. Is there any way of doing this? I've seen that you can do similar things but for only one thing at a time like sales. But I have a whole P&L (sales,cos,expenses etc) on the rows.

Any help here would be appreciated.

Kind regards

[v-jiascu-msft]

Hi @ScopseG,

Please share a dummy sample. You could achieve it with two measures using totalytd-function-dax and totalmtd-function-dax.

Best Regards,

Dale

[Sean]

You should be able to adjust my solution here!

https://community.powerbi.com/t5/Desktop/To-convert-MTD-data-to-YTD-but-with-3-variables/m-p/499261#...

[ScopseG]

Hi Sean,

Thanks for your reply.

I dont think that will work because my data set has the P&L accounts going across as opposed to being listed vertically within one column.

Is there such a functionality where in a date table, you can create something that lets you switch from MTD and YTD when a specific month is selected?

I.e the MTD and YTD would be a forumla that says something like "what ever month is selected, minus the previous months data from it for MTD"?

KR

[Sean]

In that case Unpivot the data first in the Query Editor and then adjust the above DAX solution.

http://radacad.com/pivot-and-unpivot-with-power-bi

You can always post sample data (that can be copied and pasted in PBI - not an image) or better yet share a sample PBIX

and the expected result.