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!