Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
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 )
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))).
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,
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |