Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Updating total hours for the year

Hi all,    I am currently creating a dashboard that will display the overall equipment efficiency, basically calculated by taking the downtime in comparison to available time.  However, I would li...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

    From your description, I understand that your device runs every day of the year. Now you want to get if you have no value in your data table for that date and you want to return downtime = 0 and available time = 24 for that date. Is it right?

    I think you can try to create a date table and calculate the downtime and available time by dax. 

    Date = 
VAR _T =
    ADDCOLUMNS ( CALENDARAUTO (), "Year", YEAR ( [Date] ) )
VAR _T2 =
    ADDCOLUMNS (
        _T,
        "Downtime",
            IF (
                [Date] IN VALUES ( 'Table'[Date] ),
                CALCULATE (
                    SUM ( 'Table'[Downtime] ),
                    FILTER ( 'Table', 'Table'[Date] = EARLIER ( [Date] ) )
                ),
                0
            ),
        "Available time",
            IF (
                [Date] IN VALUES ( 'Table'[Date] ),
                CALCULATE (
                    SUM ( 'Table'[Available time] ),
                    FILTER ( 'Table', 'Table'[Date] = EARLIER ( [Date] ) )
                ),
                24
            )
    )
RETURN
    _T2

    Here I use the same sample like before.

    Then you can calculate percentage by the code above.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Anonymous 

I think you want to calcualte the percentage by comparing downtime and available time.

My Sample:

Try this code.

Compression Downtime and Availabletime = 
VAR _Downtime =
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            AND ( 'Table'[Year] = MAX ( 'Table'[Year] ), 'Table'[Date] <= TODAY () )
        ),
        'Table'[Downtime]
    )
VAR _Availabletime =
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            AND ( 'Table'[Year] = MAX ( 'Table'[Year] ), 'Table'[Date] <= TODAY () )
        ),
        'Table'[Available time]
    )
RETURN
    DIVIDE ( _Downtime, _Availabletime )

 Result is as below.

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.