Forum Discussion

harshagraj's avatar
harshagraj
Post Partisan
1 year ago
Solved

Difference in Meter Reading Calculation based on dates

Hello all, Based on the below image i need some assistance either to structure the data or a calculation. If I select the Start then considering July 29, 2024 it should calculate differnce as...
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi harshagraj ,

    I updated the formula of measure in the attached pbix file, please check if that is what you want.

    Difference = 
    VAR _shifttype =
        SELECTEDVALUE ( 'Table'[ShiftStartOrEnd] )
    VAR _date =
        SELECTEDVALUE ( 'Table'[ReadingCaptureOn] )
    VAR _shift =
        SELECTEDVALUE ( 'Table'[Shift Name] )
    VAR _input =
        SELECTEDVALUE ( 'Table'[Input] )
    VAR _ss1 =
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( 'Table', 'Table'[Shift Name] = "Shift 1"&&'Table'[Input]=_input&&'Table'[ReadingCaptureOn]=_date )
        )
    VAR _ss2 =
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( ALLSELECTED('Table'), 'Table'[Shift Name] = "Shift 2"&&'Table'[Input]=_input&&'Table'[ReadingCaptureOn]=_date )
        )
    VAR _ss3 =
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER ( ALLSELECTED('Table'), 'Table'[Shift Name] = "Shift 3" &&'Table'[Input]=_input&&'Table'[ReadingCaptureOn]=_date )
        )
    VAR _es1 =
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Shift Name] = "Shift 1"
                    && 'Table'[Input] = _input
                    && 'Table'[ReadingCaptureOn] = _date + 1
            )
        )
    VAR _es2 =
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Shift Name] = "Shift 2"
                    && 'Table'[Input] = _input
                    && 'Table'[ReadingCaptureOn] = _date + 1
            )
        )
    VAR _es3 =
        CALCULATE (
            SUM ( 'Table'[Value] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Shift Name] = "Shift 3"
                    && 'Table'[Input] = _input
                    && 'Table'[ReadingCaptureOn] = _date + 1
            )
        )
    RETURN   
        SWITCH (
            TRUE (),
            _shifttype = "Start"
                && _shift = "Shift 1", _ss1 - _ss2,
            _shifttype = "Start"
                && _shift = "Shift 2", _ss2 - _ss3,
            _shifttype = "Start"
                && _shift = "Shift 3", _ss3 - _es1,
            _shifttype = "End"
                && _shift = "Shift 1", _ss3 - _es1,
            _shifttype = "End"
                && _shift = "Shift 2", _es1 - _es2,
            _shifttype = "End"
                && _shift = "Shift 3", _es2 - _es3
        )

    Best Regards