cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ElliotP
Post Prodigy
Post Prodigy

Replacement Times for Products

Evening,

 

I've attached a pbix which has the tables and things like that to work off if that's easier.

 

I have transactions for a store stamped by time, item, unique transaction ID (where there can be multiple of the same transaction IDs as there are multiple products per transaction).

 

I have my date table, my time table and of course my data table. I was wondering how to calculate at what time I will run out of product. At the moment, there isn't a table saying how much product for example is able to displayed "on the floor", but any number is fine as it's more about the principle.

 

I'm trying to work out how to calculate the average time for the item until it runs out. So for example, If I can only display 8 t shirts and by for example 11.21am I run out of stock on the floor, I'm wondering how to work this out.

 

My initial thoughts were to calcualte a measure that less'd the cumulative value by the level of stock able to be retained on the floor and then once it equalled 0 = the time; but I'm honestly not really sure how to do it. Any ideas?

 

Link to pbix: Linky Link

7 REPLIES 7
v-shex-msft
Community Support
Community Support

Hi @ElliotP,

 

Can you share some sample data to test? It hard to write a formula from your description.

 

In addition, I have an idea to deal with your requirement, perhaps you can refer to it.

 

Sample formula;

 

InRange = 
var currProduct=LASTNONBLANK('Table'[Product],[Product])
var dateRange= CALENDAR(LOOKUPVALUE('Table'[StartDate],'Table'[Product],currProduct),
LOOKUPVALUE('Table'[EndDate],'Table'[Product],currProduct))
return
CONTAINS(dateRange,[Date],MAX('DateTable'[Date]))

 

Create a matrix table ,drag date field of calendar table to Rows, the product to Columns field, measure to value field.

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

In my original post at the bottom there is a link to my pbix that I'm working with (hyperlink, I couldn't work out how to attach to the forum post).

 

I tried the formula;

 

InRange = 
var currProduct=LASTNONBLANK('itemdetailsdogfood$'[Item],[Item])
var dateRange= CALENDAR(LOOKUPVALUE('itemdetailsdogfood$'[Date],'itemdetailsdogfood$'[Item],currProduct),
LOOKUPVALUE('itemdetailsdogfood$'[Date],'itemdetailsdogfood$'[Item],currProduct))
return
CONTAINS(dateRange,[Date],MAX('ExtendedCalendar'[DateKey].[Date]))

I think I put it together correctly.

 

Then when i put the visual together as suggested I recieved this error;

 

Feedback Type:
Frown (Error)

Timestamp:
2017-02-13T10:18:25.5543159Z

Local Time:
2017-02-13T21:18:25.5543159+11:00

Product Version:
2.43.4647.541 (PBIDesktop) (x64)

Release:
February, 2017

IE Version:
11.0.9600.18015

OS Version:
Microsoft Windows NT 6.1.7601 Service Pack 1 (x64 en-US)

CLR Version:
4.6.1 or later [Release Number = 394271]

Workbook Package Info:
1* - en-US, fastCombine: Disabled, runBackgroundAnalysis: True.

Peak Working Set:
891 MB

Private Memory:
762 MB

Peak Virtual Memory:
2.33 GB

Error Message:
MdxScript(Model) (4, 13) Calculation error in measure 'itemdetailsdogfood$'[InRange]: A table of multiple values was supplied where a single value was expected.

User ID:
096e3f89-5694-4037-b08b-77a9bfdd9190

Session ID:
8504b6da-9846-4e65-8c63-bb4c709f74d4

Telemetry Enabled:
True

Model Default Mode:
Import

Snapshot Trace Logs:
C:\Users\Elliot Parker\AppData\Local\Microsoft\Power BI Desktop\FrownSnapShot1949230453.zip

Performance Trace Logs:
C:\Users\Elliot Parker\AppData\Local\Microsoft\Power BI Desktop\PerformanceTraces.zip

Disabled Preview Features:
PBI_RedShift
PBI_Impala
PBI_Snowflake
PBI_shapeMapVisualEnabled
PBI_allowBiDiCrossFilterInDirectQuery
PBI_clusteringEnabled
PBI_esriEnabled

Disabled DirectQuery Options:
DirectQuery_Unrestricted

Cloud:
GlobalCloud

Activity ID:
9527c3cb-8f6e-4ad2-fcb6-6570184f5690

Time:
Mon Feb 13 2017 21:18:18 GMT+1100 (AUS Eastern Daylight Time)

Version:
2.43.4647.541 (PBIDesktop)

Error Code:
rsDataShapeProcessingError

OData Error Message:
Failed to execute the query.

DPI Scale:
100%

Supported Services:
Power BI

Formulas:


section Section1;

shared BasicCalendarEnglish = let
    Source = Marketplace.Subscriptions(),
    #"https://api.datamarket.azure.com/Data.ashx/BoyanPenev/DateStream/v1/" = Source{[ServiceUrl="https://api.datamarket.azure.com/Data.ashx/BoyanPenev/DateStream/v1/"]}[Feeds],
    BasicCalendarEnglish_table = #"https://api.datamarket.azure.com/Data.ashx/BoyanPenev/DateStream/v1/"{[Name="BasicCalendarEnglish",Signature="table"]}[Data],
    #"Filtered Rows" = Table.SelectRows(BasicCalendarEnglish_table, each [DateKey] >= #datetime(2010, 1, 1, 0, 0, 0) and [DateKey] <= DateTime.LocalNow()),
    #"Sorted Rows" = Table.Sort(#"Filtered Rows",{{"DateKey", Order.Ascending}})
in
    #"Sorted Rows";

shared ExtendedCalendar = let
    Source = Marketplace.Subscriptions(),
    #"https://api.datamarket.azure.com/Data.ashx/BoyanPenev/DateStream/v1/" = Source{[ServiceUrl="https://api.datamarket.azure.com/Data.ashx/BoyanPenev/DateStream/v1/"]}[Feeds],
    ExtendedCalendar_table = #"https://api.datamarket.azure.com/Data.ashx/BoyanPenev/DateStream/v1/"{[Name="ExtendedCalendar",Signature="table"]}[Data],
    #"Filtered Rows" = Table.SelectRows(ExtendedCalendar_table, each [DateKey] >= #datetime(2010, 1, 1, 0, 0, 0) and [DateKey] <= DateTime.LocalNow())
in
    #"Filtered Rows";

shared HourMinuteSecond = let
    Source = Marketplace.Subscriptions(),
    #"https://api.datamarket.azure.com/Data.ashx/BoyanPenev/DateStream/v1/" = Source{[ServiceUrl="https://api.datamarket.azure.com/Data.ashx/BoyanPenev/DateStream/v1/"]}[Feeds],
    HourMinuteSecond_table = #"https://api.datamarket.azure.com/Data.ashx/BoyanPenev/DateStream/v1/"{[Name="HourMinuteSecond",Signature="table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(HourMinuteSecond_table,{{"Time", type time}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Time", "Time - Copy"),
    #"Inserted Start of Hour" = Table.AddColumn(#"Duplicated Column", "StartOfHour", each Time.StartOfHour([#"Time - Copy"]), type time),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Start of Hour",{"Time - Copy"})
in
    #"Removed Columns";

shared #"itemdetailsdogfood$" = let
    Source = Sql.Database("energydlr5kpe43dcnrgsrv.database.windows.net", "JulioJones"),
    #"dbo_itemdetailsdogfood$" = Source{[Schema="dbo",Item="itemdetailsdogfood$"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"dbo_itemdetailsdogfood$",{{"Date", type date}, {"Time", type time}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "Sections3", each if Text.Contains([Item], "Scrambled Eggs") then "Breakfast - M" else if Text.Contains([Item], "Fritters") then "Lunch - M" else if Text.Contains([Item], "Flat White") then "Coffee" else if Text.Contains([Item], "Smoothie") then "Smoothie" else if Text.Contains([Item], "Custom Amount") then "Custom Amount" else null ),
    #"Added Conditional Column1" = Table.AddColumn(#"Added Conditional Column", "Sections2", each if Text.Contains([Sections3], "Breakfast - M") then "Meal" else if Text.Contains([Sections3], "Coffee") then "Barista" else if Text.Contains([Sections3], "Lunch - M") then "Meal" else if Text.Contains([Sections3], "Smoothie") then "Bar" else if Text.Contains([Sections3], "Custom Amount") then "Custom Amount" else null ),
    #"Added Conditional Column2" = Table.AddColumn(#"Added Conditional Column1", "Sections1", each if Text.Contains([Sections2], "Meal") then "Kitchen" else if Text.Contains([Sections2], "Barista") then "Drink" else if Text.Contains([Sections2], "Bar") then "Drink" else if Text.Contains([Sections2], "Custom Amount") then "Custom Amount" else null )
in
    #"Added Conditional Column2";

shared #"transactionsdogfood$" = let
    Source = Sql.Database("energydlr5kpe43dcnrgsrv.database.windows.net", "JulioJones"),
    #"dbo_transactionsdogfood$" = Source{[Schema="dbo",Item="transactionsdogfood$"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(#"dbo_transactionsdogfood$",{{"Date", type date}, {"Time", type time}})
in
    #"Changed Type";

shared #"itemdetailsdogfood$tidgrp" = let
    Source = Sql.Database("energydlr5kpe43dcnrgsrv.database.windows.net", "JulioJones"),
    #"dbo_itemdetailsdogfood$" = Source{[Schema="dbo",Item="itemdetailsdogfood$"]}[Data],
    #"Grouped Rows" = Table.Group(#"dbo_itemdetailsdogfood$", {"Transaction ID", "Date"}, {{"Count", each Table.RowCount(Table.Distinct(_)), type number}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Grouped Rows",{{"Date", type date}})
in
    #"Changed Type";

shared Sheet1 = let
    Source = Excel.Workbook(File.Contents("C:\Users\Elliot Parker\Desktop\modifierlist.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Modifier Category", type text}, {"Name", type text}, {"Price", type number}})
in
    #"Changed Type";

I've attached my pbix again if this helps; My Pbix

 

Hi @ElliotP,

 

Since your table not contain the start date and end date of the product, I'm use the min and max date of product as the date range.

 

I modify the formula and try to use search function.(it seems like contains function not work)

 

InRange = 
var currProduct=LASTNONBLANK('itemdetailsdogfood$'[Item],[Item])
var dateRange= CALENDAR(MINX(FILTER(ALL('itemdetailsdogfood$'),[Item]=currProduct),[Date]),
MAXX(FILTER(ALL('itemdetailsdogfood$'),[Item]=currProduct),[Date]))
return
ISERROR(SEARCH(CONCATENATEX(dateRange,[Date],","),FORMAT(MAX('ExtendedCalendar'[DateKey]),"M/d/yyyy")))

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Since I'm not using an inventory management system I'd like to use sound assumptions for the values. Such as, there is only 8 beers on stock, as so I'm trying to work out roughly what time of the day it takes to sell 8 beers.

 

The idea I had was to start to work out the cumulative amount of beers sold. Then use an IF function from a table which contains the item name (beer for example) and it's level of stock on hand (8) and how long it takes to sell that amount. And then apply this to a Time value as so I would be able to calculate for example the average Time (such as 7.21am).

 

 

It would be ideal to create a seperate table I think with the item name and the amount of stock on hand as so it's not a matter of creating a large number of measures, but rather just changing the filter.

Thoughts?

Hi @ElliotP,

 

You can calculate based on above measure (its used to calculate daily stock in range).

 

Filter by date and calculate the count for "true" result of above measure.

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Thanks for the response;

 

I'm not sure how to do that sorry. I'm not sure how to take that formula, apply it as a filter and then find the time, etc.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.