Calculations based on different data granularity


#1

hello guys,

i need some help to resolve my issue with data at diffrent granularity, I have prepared pbix with data model and test data, which you can find at below link: https://ufile.io/0846h

I have 4 objects:

  • Date - standard date table
  • Products data
  • Sales - some product sales at day level
  • Discounts - product discounts value definied at month level for specific year

What I want to achive is to calculate following formula: ** (Total sales for given product) x (Product discount value at given time) - (Total costs for given product) x (Product discount % value at given time)**

If I choose i.e. dates for 2018, I want to see calculated value for all products at month levels with posibility to slice by product and specific month and day

any ideas?

Regards


#2

Check out this tutorial here. This explains all the unique this you need to do to make this analysis happen.

There’s a little bit to it but very doable


#3

Another good one to watch that cover handling very specific days is this one below

It’s not exactly your scenario but many of the tips are very transferable over to what you’re trying to achieve.