calculations based on different data granularity

[Anonymous]

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 measure: (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

[SaumyaShetty · Accepted solution]

Hi,

Please check the file int the below link.Does this suffice your requirement.

https://uploadfiles.io/8pkjq

Regards,

Saumya Shetty

[Anonymous]

thanks for reply 🙂 is there any other solution without using bi-directional relationships?

Moreover, there was an mistake in provided model, because beside Val column in Discount table there should be also percentages values.. sorry for that, I have already extended model and updated the desired result

[Ashish_Mathur]

Hi,

The download link in your orogonal post does not work.

[Anonymous]

it should not be a problem with link from my original post, please find below the same file uploaded on dropbox:

https://www.dropbox.com/s/259epccwbv7c247/discounts.pbix?dl=0

Regards!

[Ashish_Mathur]

Hi,

In the Discount table, the %val column should be a % column.  In your file, it is a text column.  In the Query Editor make that change and share the download link again.  Also, in a simple table, show the exact result you are expecting.