Data Model Retail Business (help!!)

Hi Anonymous,

I mocked up a disconnected dimension approach to make the stock value by day inventory a little more dynamic which will allow you to perform date look backs a little more on the fly without having to create columns for each permutation.

Solution as follows:

Disconnected Dimension - Create Dax Calc Table:

Coverage = DATATABLE(
        "Coverage", STRING, 
        "Coverage Index", INTEGER, 
        {
            {"More than 50 Days", 3},
            {"No Sales", 4},
            {"Less than 20 Days", 1},
            {"More than 20 Days", 2}
        }
    )

Current Period Measure:

stock Value Dynamic CP = 
var tbl = ADDCOLUMNS('F_Stock',"coverage", divide([Stock value], [Sales value], 0)*30, "visible", 
    switch(true(),
        [Sales value] = BLANK() && min(Coverage[Coverage Label]) = "No Sales", [Stock value],
        [Sales value] > 0 && divide([Stock value], [Sales value])*30 < 20 && min(Coverage[Coverage Label]) = "Less than 20 Days", [Stock value],
        [Sales value] <> 0 && divide([Stock value], [Sales value])*30 >=20 && divide([Stock value], [Sales value])*30 < 50 && min(Coverage[Coverage Label]) = "Less than 50 Days", [Stock value],    
        [Sales value] > 0 && divide([Stock value], [Sales value])*30 >= 50 && min(Coverage[Coverage Label]) = "More than 50 Days", [Stock value] 
        ))
var ret = if(HASONEVALUE(Coverage[Coverage Label]), CALCULATE(SUMX(tbl, [visible])), sum([Stock value]))
return
ret

Current Period Measure Prior Period:

stock Value Dynamic CP = 
var tbl = ADDCOLUMNS('F_Stock',"coverage", divide([Stock value], [Sales value], 0)*30, "visible", 
    switch(true(),
        [Sales value] = BLANK() && min(Coverage[Coverage Label]) = "No Sales", [Stock value],
        [Sales value] > 0 && divide([Stock value], [Sales value])*30 < 20 && min(Coverage[Coverage Label]) = "Less than 20 Days", [Stock value],
        [Sales value] <> 0 && divide([Stock value], [Sales value])*30 >=20 && divide([Stock value], [Sales value])*30 < 50 && min(Coverage[Coverage Label]) = "Less than 50 Days", [Stock value],    
        [Sales value] > 0 && divide([Stock value], [Sales value])*30 >= 50 && min(Coverage[Coverage Label]) = "More than 50 Days", [Stock value] 
        ))
var ret = if(HASONEVALUE(Coverage[Coverage Label]), CALCULATE(SUMX(tbl, [visible])), sum([Stock value]))
return
ret

Couple Caveats:

- Date Math is not very robust just subtracts one from the Max Month in context

- Better to have a YearMonth column in the date table like, 202010, then subtracting 100 will always return the prior month

- Assumes that Month will be in the context as it uses Max to get current month and then subtracts 1, may be better to check to see if Month has a single value and then perform the calc else return blank()

I all new formulas saved to shared pbix

Hope this helps you out,

Richard

Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Hi,

Thanks for the input!

I will test it and then came to give feedback.

Cheers and thank you again

Hi richbenmintz 

The solution is working perfectly!

I was trying on the final model to show only in percentages. I donne it but know i found an error.

For example, in the month 11, Paris Store. Yellow highlighted.

I was trying to just show the "More than 50 days" and the "No sales" on the table below and, at the same time, the percentages of these two "labels", according with TOTAL stock of the store (and not just the ones i selected "more than 50 days" and the "no sales").

However, when i select the "no sales" and the "more than 50 days" i have a % of 100% for the total of the store. However the righ percentage should be 128 / 179 = 71,5% and not 128 /128 = 100%. I hope i make myself clear on these explanation. Is there any chance to bypass this?

Cheers!

Hi Anonymous,

Can you provide the measure you created? will be easier for me to understand what needs to be changed.

Thanks,