Forum Discussion

TDys's avatar
TDys
Frequent Visitor
6 years ago
Solved

Utilization hours difference between following days

Hi All;

 

I am trying to calculate hours difference between following days for machines daily utilization. Fleet of assets is reporting total hours at the end of each day. I want to be able to see daily working hours for each asset. I was trying formula below but it is not ok.

 

Thank you in advance.

Hours Difference Between Dates = 
VAR TheFirstAmount =
    CALCULATE(
        MIN('EquipmentSMU_API_Daily'[SMU]),
        FILTER(
            'EquipmentSMU_API_Daily',
            MIN('EquipmentSMU_API_Daily'[TRANSDATE].[Date]) >= FIRSTDATE(ALLSELECTED('EquipmentSMU_API_Daily'[TRANSDATE].[Date]))
        )
    )
VAR TheLastAmount =
  CALCULATE(
        MAX('EquipmentSMU_API_Daily'[SMU]),
        FILTER(
            'EquipmentSMU_API_Daily',
            MAX('EquipmentSMU_API_Daily'[TRANSDATE].[Date]) >= FIRSTDATE(ALLSELECTED('EquipmentSMU_API_Daily'[TRANSDATE].[Date]))
        )
    )
RETURN
TheLastAmount - TheFirstAmount

 

Need Help
  • TomMartens's avatar
    TomMartens
    6 years ago

    Hey TDys ,

     

    this DAX allows is creating a calculated column:

     

    previous smu = 

var instance = 'EquipmentSMU_API_Daily'[SERIALID]
var currentDate = EquipmentSMU_API_Daily[TRANSDATE]
var smu = 'EquipmentSMU_API_Daily'[Total SMU]
var prevdate =
    MAXX(
        FILTER(
            ALL(EquipmentSMU_API_Daily)
            , 'EquipmentSMU_API_Daily'[SERIALID] = instance && 'EquipmentSMU_API_Daily'[TRANSDATE] < currentDate
        )
        , [TRANSDATE]
    )
return
SUMX(
        FILTER(
            ALL(EquipmentSMU_API_Daily)
            , 'EquipmentSMU_API_Daily'[SERIALID] = instance && 'EquipmentSMU_API_Daily'[TRANSDATE] = prevdate
        )
        , [Total SMU]
    )

     

    Here is a screenshot from the table of your sample pbix (please be aware that a serialid is filtered and sorted (ascending) by TRANSDATE:

    It will be very easy to calculate the change, just put "smu -" in front of the final SUMX.

    A  notion, solving this kind of problem (I call them, sorted (by something, here TRANDATE) sets (a set of something, here TRANDATE, grouped by SERIALID) is not the domain of the DAX engine where it could shine as it can. As the number of rows will grow, creating the column value will get slower and slower. Then you have to consider different approaches, like incremental refresh, or even a different architecture. 

     

    Nevertheless, I'm hoping this will get you started.

    Regards,
    Tom

4 Replies

  • TomMartens's avatar
    TomMartens
    Icon for Super User rankSuper User
    6 years ago

    Hey TDys ,

     

    can you please upload create a pbix file that contains some sample data, but still reflects your data model. Upload the pbix to onedrive or dropbox and share the link.

     

    Will there be a record for each machine each day, or can it be possible that a machine is not utilized on one or more consecutive days, or phrasing my question otherwise: is it possible to discover the previous day by subtracting -1 from the date in the current row.

     

    Regards,

    Tom

    • TDys's avatar
      TDys
      Frequent Visitor
      6 years ago

      Hello Tom;

       

      Thank you so much for quick replay. Below please find link to file with sample data.

       

      https://www.dropbox.com/s/03rpegvzs3hlltr/SMU%20Calculation.pbix?dl=0

       

      And yes it is possible that machine is not creating any record for number of days when it is not utilized. Also machine can create record with same SMU value for number of days if key switch was left on but engine was not running. Data are being send at the end of each day.

       

      Best regards;

      Tomasz

      • TomMartens's avatar
        TomMartens
        Icon for Super User rankSuper User
        6 years ago

        Hey TDys ,

         

        this DAX allows is creating a calculated column:

         

        previous smu = 

var instance = 'EquipmentSMU_API_Daily'[SERIALID]
var currentDate = EquipmentSMU_API_Daily[TRANSDATE]
var smu = 'EquipmentSMU_API_Daily'[Total SMU]
var prevdate =
    MAXX(
        FILTER(
            ALL(EquipmentSMU_API_Daily)
            , 'EquipmentSMU_API_Daily'[SERIALID] = instance && 'EquipmentSMU_API_Daily'[TRANSDATE] < currentDate
        )
        , [TRANSDATE]
    )
return
SUMX(
        FILTER(
            ALL(EquipmentSMU_API_Daily)
            , 'EquipmentSMU_API_Daily'[SERIALID] = instance && 'EquipmentSMU_API_Daily'[TRANSDATE] = prevdate
        )
        , [Total SMU]
    )

         

        Here is a screenshot from the table of your sample pbix (please be aware that a serialid is filtered and sorted (ascending) by TRANSDATE:

        It will be very easy to calculate the change, just put "smu -" in front of the final SUMX.

        A  notion, solving this kind of problem (I call them, sorted (by something, here TRANDATE) sets (a set of something, here TRANDATE, grouped by SERIALID) is not the domain of the DAX engine where it could shine as it can. As the number of rows will grow, creating the column value will get slower and slower. Then you have to consider different approaches, like incremental refresh, or even a different architecture. 

         

        Nevertheless, I'm hoping this will get you started.

        Regards,
        Tom