how to calculate time taken to sell particular quantity

[bicoffee]

i have bill wise sales data where i want to find time taken to sell first 10 quantity. FIND BELOW MY TABLE structure. Here i want to find to sell 10 qty how much time they have taken. By seeing table we can manually arrive he achieved 10 quantity at 02:21:35 PM.

DATE CAFEID BILLNO BILLTIME PRODUCT QUANTITY
01-01-2020 C1 22773 12:16:23 PM A1 1
01-01-2020 C1 22779 12:46:36 PM A1 3
01-01-2020 C1 22783 01:08:36 PM A1 2
01-01-2020 C1 22784 01:22:00 PM A1 1
01-01-2020 C1 22788 01:58:45 PM A1 1
01-01-2020 C1 22791 02:21:35 PM A1 2
01-01-2020 C1 22804 04:22:14 PM A1 1
01-01-2020 C1 22812 05:05:22 PM A1 1
01-01-2020 C1 22821 05:59:26 PM A1 1

[bicoffee]

Hi, thanks for the quick revert.. I think you have missed the stores...currently your solutions works on date...but require for each date and store... 

[Jihwan_Kim · Accepted solution]

https://www.dropbox.com/s/qei8dwbeyac3cgf/bicoffee.pbix?dl=0 

Please kindly check the above link if it is correctly fixed or not.

Thank you very much.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[bicoffee]

Dear Jihwan,

  Solution works fine but only thing is it is taking lot of time to calculate the result. Is there any solution ? Can i have the result in different table so that it will reduce calculation time evertime ? or can we have new column insted of measures ? Kindly suggest.

[Jihwan_Kim]

Hi,

Thank you for your feedback.

By using my sample data, I could not find the bottleneck for the slow performance by using Performance Analyzer. I think it is because my sample has too little information.

May I see your pbix file? How do you connect your date column and time column with your dim-date-table and dim-time-table?

Thank you.

[bicoffee]

FIND BELOW DAX QUERY ...VISUAL IS TAKING 223934 MS

// DAX Query
DEFINE
VAR __DS0FilterTable =
FILTER(
KEEPFILTERS(VALUES('BILLWISE DATE'[DATE])),
AND(
'BILLWISE DATE'[DATE] >= DATE(2021, 4, 4),
'BILLWISE DATE'[DATE] < DATE(2021, 4, 5)
)
)

VAR __DS0FilterTable2 =
TREATAS({"PHASE-1"}, 'CAFE_OUTLET_MASTER'[PRICEHIKE_CAFE])

VAR __DS0Core =
FILTER(
KEEPFILTERS(
SELECTCOLUMNS(
KEEPFILTERS(
FILTER(
KEEPFILTERS(
SUMMARIZECOLUMNS(
'SBSALE'[DATE],
'SBSALE'[BILLTIME],
'SBSALE'[CAFE NAME],
__DS0FilterTable,
__DS0FilterTable2,
"First_10_qty_time", IGNORE('SBSALE'[First 10 qty time]),
"CountRowsSBSALE", CALCULATE(COUNTROWS('SBSALE'))
)
),
OR(
OR(
NOT(ISBLANK('SBSALE'[DATE])),
NOT(ISBLANK('SBSALE'[BILLTIME]))
),
NOT(ISBLANK('SBSALE'[CAFE NAME]))
)
)
),
"'SBSALE'[DATE]", 'SBSALE'[DATE],
"'SBSALE'[BILLTIME]", 'SBSALE'[BILLTIME],
"'SBSALE'[CAFE NAME]", 'SBSALE'[CAFE NAME],
"First_10_qty_time", [First_10_qty_time]
)
),
NOT(ISBLANK([First_10_qty_time]))
)

VAR __DS0PrimaryWindowed =
TOPN(
501,
__DS0Core,
'SBSALE'[DATE],
1,
'SBSALE'[BILLTIME],
1,
'SBSALE'[CAFE NAME],
1
)

EVALUATE
__DS0PrimaryWindowed

ORDER BY
'SBSALE'[DATE],
'SBSALE'[BILLTIME],
'SBSALE'[CAFE NAME]

[Jihwan_Kim]

Hi,

Terribly sorry to say that it is difficult for me to understand what you have written in DAX Studio.

[Jihwan_Kim]

Hi, 

Your sample says all cafe IDs are the same. so that was why I created measures in that way to simplify those.

Inside each measure, inputting one more factor to consider which is CafeID will fix the problem.

thanks.

[Jihwan_Kim]

Hi, @bicoffee 

Please check the below picture and the link down below, that is the sample pbix file.

all measures are in the sample file.

https://www.dropbox.com/s/qei8dwbeyac3cgf/bicoffee.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.