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

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
Super 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

  • TDys's avatar
    TDys
    Frequent Visitor
    6 years ago

    Thank you very much Tom. This is exactly what I needed. 

     

    Best regards;

    Tomasz