How best to organise data query

[bitbit]

My database is currently organised as follows:

Country	Year	Version	Currency	P&L line	Month	Value
ABC	2018	Actual	Local currency	Net Sales	Jan	xx
ABC	2018	Actual	Eur	Net Sales	Jan	xxx

Country: 20 countries

Year: 2018-2022

Version: Actual, Budget

Currency: local, USD (conso)

P&L line: Sales to net profit

Month: Jan - Dec

Value = data

I want to create a P&L pivot table using Power Query to show MTD, YTD & Total Year, key ratios (eg. gross margin % sales etc) as well as the comparative variances (eg. actual vs budget vs prior years).  P&L is to be sliceable by country, currency, month.

I am unable to create calculated fields for key ratios (field "greyed out"). I also struggle to create DAX formula for MTD, YTD and Total Year.

Would greatly appreciate some advice on how to create this in the simplest way.

[ALLUREAN · Accepted solution]

Hi, @bitbit 

You have 1 fact table. In best practice you should separate it by Dimension tables (e.g. PnL Line, Country, Date table etc). This can be achieved in a couple of ways (Power Query or Dax). Then dimension tables will be connected to your fact table, containing repeating values. Then you can create your time-intelligence measures like MTD/YTD based on your Date table and for P&L calculations also you can create measures to dynamically calculate Gross Margin for example (if not already calculated).

If you provide example file I can create a simple model and make some of the calculations, but this file should have the same structure of your original data in order to work