Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Data Model Retail Business (help!!)

Hi Guys,

 

I work on the retail business (several stores and several products) and i would like to make a report about:

1) Products with NO SALES

2) Products with stock coverage:

Products with > 50 days of stock

Producst with 20 to 50 days of stock

Products with < 20 days of stock

 

On my datal model i have:

Fact tables:

Stock table (data granularity - month) 

Sales table (data granularity - month) 

Dimension tables:

Data

Produt

 

Assumptions:

Stock coverage = Stock of product x at the store A dividing by Sales of the product x at the Store A

Stock with no sales = Product who make part of the products table but doesnt have any record of sales on the same month. 

 

So acording with this assumptions im dealing with a big problem (at least for me obviouslly...).  To calculate the "Stock coverage" its necessary to make  calculations on rows (X Functions), but i have no chance to make them between diferent fact tables (stocks and sales). So how is the best way to do it?

 

Thank you so much!

 

 

 

 

 

 

 

 

 

 

 

 

Need Help
Tips and Tricks

13 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Anonymous ,Can you share sample data and sample output in table format?

     

    for no sales you can try like

    if( isblank(CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))) && CALCULATE(SUM(Item[Qty]),DATESMTD('Date'[Date])) >0, "Stock with no sales",blank())