Forum Discussion

afi_imco's avatar
afi_imco
Frequent Visitor
7 years ago

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...
Need Help
Tips and Tricks
AlB's avatar
AlB
Community Champion
7 years ago

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  

 

afi_imco's avatar
afi_imco
Frequent Visitor
7 years ago

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

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    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,