Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
afi_imco
Frequent Visitor

Help! Time difference & percentage calculations

Hi experts!

 

Again here asking for your help!

 

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?

 

Thanks for your help!

3 REPLIES 3
AlB
Super User
Super User

Hi @afi_imco

 

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   www.daxformatter.com

 

afi_imco
Frequent Visitor

Hi @AlB,

 

Thanks for your help...

 

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))).

@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.

 

instead of

IF( OOS_Calculation[BOOS_Stock] = 0,

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.