new business reporting - Identify new store locations and new product orders

[Kieran_choudary]

Hi community,

Im trying to show new product order from stores for orders with revenue over 100$ (qualifying for comms) so if a store orders a new product in the current month it will count 1,

I would also like to do the same but for new store locations.

Does anyone know if this is possible?

[CahabaData]

I believe it is possible.  I can only add a few general high level comments:

"stores for orders with revenue over 100$" : it is not clear if you mean a Store with revenue over 100 as in a Big Store which would be an identity flag - or you mean an Order over 100 - or if you mean a cumulative Order amount within a defined time frame (i.e. current month) : any one of those is possible but the implementation differs depending on what is intended.

likewise:

"if a store orders a new product in the current month it will count 1" :  do you mean any order in the current month or the first order only in the current month or the order of a designated "new product"

in regard to:

"do the same but for new store locations" : one would need to define & flag "new stores" as part of the data source or in the data model based on a calculation

[Kieran_choudary]

so the over 100 is because any previous orders a store may have had that was under 100 its would be samples not an actualy order, so i guess it would be the first order over 100 of a product

for your second point, the count would show in the month that the store made thier first order over 100,

for new stores, is there not a way to identify the first instance that store location shows up with revenue over 100 just the same as product but at the store column 

[TomMartens]

Hey,

I would start with adding two date fields to your product table: [StartDateComms] and [EndDateComms].

Assuming this product table relates to your fact table (order table), you can now easily calculate a new column in the order table checking if the order date falls between both dates and the order amout is greater than 100$, in this case your calculated column returns 1

[Kieran_choudary]

Hi and thank you for you response, i dont have a product table, my query puts all the sales data in one table,

the catergories are the kpi, but that table just contains SKUs and catorgery name, no dates, would it go in there?

Also could you advise on the formula you are describing?

[TomMartens]

Hey,

here you can find a little solution

https://www.dropbox.com/sh/d6kuoy7i1u3oylj/AADunwesu3Vp0ETobwM3uYIla?dl=0

Your question, where the dates belong, that indicate the date range for new products: I would put it into your table categories, as far as i understand this is the "product" table I mentioned.

The pbix-file also contains a calculated column in the table "Query1" (the fact table). This calculation evaluates if the orderdate is between the dates that allow for comms. This calculation also checks the orderamount.

Hope this helps

[Kieran_choudary]

Hi an thank you for you response, i dont have a product table, my query puts all the sales data in one table,

the catergories are the kpi, but that table just contains SKUs and catorgery name, no dates, would it go in there?

Also could you advise on the formula you are describing?