How to design this data model?

[Raul]

Hello everybody,

I would like to know how I can design this  data model:

I have a table (Areas) that have this values for date year <=2017 (Area1, Area2, Area3) and this others values for date year >=2018 (Area1, Area2, Area4, Area5). I have another table (Invoices) with this fields: InvoiceNum, InvoiceDate, InvoiceAmount, Area and I want to compare amount values for a period, for example, actual year amount invoice versus previous year amount invoice just like that:

The Amount previous year it's a measure wtih this formula:

=CALCULATE(SUM(Invoices[InvoiceAmount]), PREVIOUSYEAR(Invoices[InvoiceDate]))

And I have this data model:

It is possible? How should the structure of the Areas table be? The Amount previous year measure is it correct?

Thank you very much.

[v-juanli-msft · Accepted solution]

Hi @Raul

Please have a look at my pbix, if you have any question, feel free to ask me.

 

Best Regards

Maggie

[Raul]

OMG! It's perfect!!!

Thank you very much @v-juanli-msft for your reponse and clear exemple. I will apply your model in mine and tell you if it worked as in your example.

Thanks a lot!