cancel
Showing results for
Did you mean:
Frequent Visitor

Help! Time difference & percentage calculations

Hi experts!

This is a project for the retail industry and I'm just getting to know Power BI and DAX and I need to find a way to get some calculations.

The information that contains the table is generated by sensors and the sensor generate data when somebody takes or remove items from shelves. The idea is to calculate out of stock metrics!

I have the following table:

1.- I need to calculate the time difference in minutes between two different dates. The out of stock "time" starts when the field BOOS_Stock is "0" and ends when BOOS_Stock is "1".

2.- I need to calculate out of stock %. As you know, not all the stores are open 24 hrs. which means that I have to consider that the store is open Monday to Saturday, 8 to 20 Hrs.

Any idea or advice of how can I improve these calculations in my project?

3 REPLIES 3
Super User

Re: Help! Time difference & percentage calculations

Please always show your sample data in text-table format as well so that it can be readily copied.

You could try adding a new calculated column to your table as indicated below. This column will have the number of minutes of out of stock (OOS) in each line where an OOS starts (i.e.  where BOOS_Stock is 0). The code assumes that each OOS is calculated per stored and per product. You can then use this column to calculate the OOS % as you please. You haven't explained how exactly that would be done.

```OOSMinutes =
IF (
Table1[BOOS_Stock] = 0,
VAR _OOSEndTime =
CALCULATE (
MIN ( Table1[BOOS_AngeletAm] ),
Table1[BOOS_AngeletAm] > EARLIER ( Table1[BOOS_AngeletAm] ),
ALL ( Table1[BOOS_Stock] )
)
VAR _OOSStartTime = Table1[BOOS_AngeletAm]
VAR _OOSTimeInMins = ( _OOSEndTime - _OOSStartTime )
* 24
* 60
RETURN
_OOSTimeInMins
)```

Code formatted with

Frequent Visitor

Re: Help! Time difference & percentage calculations

Hi @AlB,

I'm trying to improve your advice but I have a syntax error for the 'VAR'.

```OOSMinutes =
IF (
OOS_Calculation[BOOS_Stock] = 0,
VAR OOSEndTime =
CALCULATE (
MIN (OOS_Calculation[BOOS_AngelegtAm]),
OOS_Calculation[BOOS_AngelegtAm] > EARLIER (OOS_Calculation[BOOS_AngelegtAm]),
ALL (OOS_Calculation[BOOS_Stock])
)
VAR OOSStartTime = OOS_Calculation[BOOS_AngelegtAm]
VAR OOSTimeInMins = (OOSEndTime - OOSStartTime)
* 24
* 60
RETURN
OOSTimeInMins
)```

The error:

The syntax for 'VAR' is incorrect. (DAX(IF ( OOS_Calculation[BOOS_Stock] = 0. VAR OOSEndTime = CALCULATE ( MIN (OOS_Calculation[BOOS_AngelegtAm]), OOS_Calculation[BOOS_AngelegtAm] > EARLIER (OOS_Calculation[BOOS_AngelegtAm]), ALL (OOS_Calculation[BOOS_Stock]) ) VAR OOSStartTime = OOS_Calculation[BOOS_AngelegtAm] VAR OOSTimeInMins = (OOSEndTime - OOSStartTime) * 24 * 60 RETURN OOSTimeInMins))).

Super User

Re: Help! Time difference & percentage calculations

@afi_imco

From the error message it seems that you have a dot "." instead of a comma "," at the end of

OOS_Calculation[BOOS_Stock] = 0

i.e. you seem to have

IF( OOS_Calculation[BOOS_Stock] = 0.

IF( OOS_Calculation[BOOS_Stock] = 0,

Announcements

Community News & Announcements

Get your latest community news and announcements.

Power Platform Summit North America

Register by September 5 to save \$200

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 180 members 1,803 guests
Recent signins: