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 Shift1-Shift2 at Shift1, Shift2-Shift3 at Shift2 and Shift3-Shift1(July 30,2024) at Shift 3 (July 29,2024) If I select the End then considering July 29, 2024 it should calculate differnce as Shift3(July 29,2024)-Shift1(July 30,2024) at Shift1 (July 30,2024), Shift1(July 30,2024)-Shift2(July 30,2024) at Shift2 (July 30,2024) and Shift2(July 30,2024)-Shift3(July 30,2024) at Shift 3(July 30,2024)Please guide me how to solve this.Attaching sample data.User IDUser NameReadingCaptureOnShift NameShiftStartOrEndInputValue174913Jon7/28/2024Shift 3StartP12244567174913Jon7/28/2024Shift 3StartP32344567174913Jon7/28/2024Shift 3StartP21962234175833Bart7/29/2024Shift 1StartP12943326175833Bart7/29/2024Shift 1StartP32989733175833Bart7/29/2024Shift 1StartP23042042175833Bart7/29/2024Shift 2EndP12281906175833Bart7/29/2024Shift 2EndP32823592175833Bart7/29/2024Shift 2EndP22969746174913Jon7/29/2024Shift 2StartP12782375174913Jon7/29/2024Shift 2StartP32982522174913Jon7/29/2024Shift 2StartP23020714174913Jon7/29/2024Shift 2EndP12802801174913Jon7/29/2024Shift 2EndP32605439174913Jon7/29/2024Shift 2EndP22906316174557Wan7/29/2024Shift 2StartP12632718174557Wan7/29/2024Shift 2StartP32272392174557Wan7/29/2024Shift 2StartP22941521174557Wan7/29/2024Shift 3StartP12683621174557Wan7/29/2024Shift 3StartP32982543174557Wan7/29/2024Shift 3StartP22894587174557Wan7/30/2024Shift 3EndP11335543174557Wan7/30/2024Shift 3EndP31160300174557Wan7/30/2024Shift 3EndP23326923175833Bart7/30/2024Shift 1StartP11206540175833Bart7/30/2024Shift 1StartP31050958175833Bart7/30/2024Shift 1StartP23326974175833Bart7/30/2024Shift 1EndP11242837175833Bart7/30/2024Shift 1EndP31081205175833Bart7/30/2024Shift 1EndP23326924174913Jon7/30/2024Shift 2StartP11242837174913Jon7/30/2024Shift 2StartP31081205174913Jon7/30/2024Shift 2StartP23326924174557Wan7/30/2024Shift 3StartP11282022174557Wan7/30/2024Shift 3StartP31115002174557Wan7/30/2024Shift 3StartP23326924174913Jon7/30/2024Shift 3EndP11282022174913Jon7/30/2024Shift 3EndP31115002174913Jon7/30/2024Shift 3EndP23326924174913Jon7/30/2024Shift 2EndP11282022174913Jon7/30/2024Shift 2EndP31115002174913Jon7/30/2024Shift 2EndP23326924175833Bart7/31/2024Shift 1StartP11335543175833Bart7/31/2024Shift 1StartP31160300175833Bart7/31/2024Shift 1StartP23326924175833Bart7/31/2024Shift 1EndP11367492175833Bart7/31/2024Shift 1EndP31186612175833Bart7/31/2024Shift 1EndP23329164174913Jon7/31/2024Shift 2StartP11367492174913Jon7/31/2024Shift 2StartP31186612174913Jon7/31/2024Shift 2StartP23329164175422Peter7/31/2024Shift 3StartP11374583175422Peter7/31/2024Shift 3StartP31194139175422Peter7/31/2024Shift 3StartP23351105174913Jon7/31/2024Shift 2EndP11374583174913Jon7/31/2024Shift 2EndP31194139174913Jon7/31/2024Shift 2EndP23351105175422Peter7/31/2024Shift 3EndP11423881175422Peter7/31/2024Shift 3EndP31234736175422Peter7/31/2024Shift 3EndP23351105 amitchandak VijayPerepa

Forum Discussion

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 Shift1-Shift2 at Shift1, Shift2-Shift3 at Shift2 and Shift3-Shift1(July 30,2024) at Shift 3 (July 29,2024)
If I select the End then considering July 29, 2024 it should calculate differnce as Shift3(July 29,2024)-Shift1(July 30,2024) at Shift1 (July 30,2024), Shift1(July 30,2024)-Shift2(July 30,2024) at Shift2 (July 30,2024) and Shift2(July 30,2024)-Shift3(July 30,2024) at Shift 3(July 30,2024)

Please guide me how to solve this.Attaching sample data.

User ID	User Name	ReadingCaptureOn	Shift Name	ShiftStartOrEnd	Input	Value
174913	Jon	7/28/2024	Shift 3	Start	P1	2244567
174913	Jon	7/28/2024	Shift 3	Start	P3	2344567
174913	Jon	7/28/2024	Shift 3	Start	P2	1962234
175833	Bart	7/29/2024	Shift 1	Start	P1	2943326
175833	Bart	7/29/2024	Shift 1	Start	P3	2989733
175833	Bart	7/29/2024	Shift 1	Start	P2	3042042
175833	Bart	7/29/2024	Shift 2	End	P1	2281906
175833	Bart	7/29/2024	Shift 2	End	P3	2823592
175833	Bart	7/29/2024	Shift 2	End	P2	2969746
174913	Jon	7/29/2024	Shift 2	Start	P1	2782375
174913	Jon	7/29/2024	Shift 2	Start	P3	2982522
174913	Jon	7/29/2024	Shift 2	Start	P2	3020714
174913	Jon	7/29/2024	Shift 2	End	P1	2802801
174913	Jon	7/29/2024	Shift 2	End	P3	2605439
174913	Jon	7/29/2024	Shift 2	End	P2	2906316
174557	Wan	7/29/2024	Shift 2	Start	P1	2632718
174557	Wan	7/29/2024	Shift 2	Start	P3	2272392
174557	Wan	7/29/2024	Shift 2	Start	P2	2941521
174557	Wan	7/29/2024	Shift 3	Start	P1	2683621
174557	Wan	7/29/2024	Shift 3	Start	P3	2982543
174557	Wan	7/29/2024	Shift 3	Start	P2	2894587
174557	Wan	7/30/2024	Shift 3	End	P1	1335543
174557	Wan	7/30/2024	Shift 3	End	P3	1160300
174557	Wan	7/30/2024	Shift 3	End	P2	3326923
175833	Bart	7/30/2024	Shift 1	Start	P1	1206540
175833	Bart	7/30/2024	Shift 1	Start	P3	1050958
175833	Bart	7/30/2024	Shift 1	Start	P2	3326974
175833	Bart	7/30/2024	Shift 1	End	P1	1242837
175833	Bart	7/30/2024	Shift 1	End	P3	1081205
175833	Bart	7/30/2024	Shift 1	End	P2	3326924
174913	Jon	7/30/2024	Shift 2	Start	P1	1242837
174913	Jon	7/30/2024	Shift 2	Start	P3	1081205
174913	Jon	7/30/2024	Shift 2	Start	P2	3326924
174557	Wan	7/30/2024	Shift 3	Start	P1	1282022
174557	Wan	7/30/2024	Shift 3	Start	P3	1115002
174557	Wan	7/30/2024	Shift 3	Start	P2	3326924
174913	Jon	7/30/2024	Shift 3	End	P1	1282022
174913	Jon	7/30/2024	Shift 3	End	P3	1115002
174913	Jon	7/30/2024	Shift 3	End	P2	3326924
174913	Jon	7/30/2024	Shift 2	End	P1	1282022
174913	Jon	7/30/2024	Shift 2	End	P3	1115002
174913	Jon	7/30/2024	Shift 2	End	P2	3326924
175833	Bart	7/31/2024	Shift 1	Start	P1	1335543
175833	Bart	7/31/2024	Shift 1	Start	P3	1160300
175833	Bart	7/31/2024	Shift 1	Start	P2	3326924
175833	Bart	7/31/2024	Shift 1	End	P1	1367492
175833	Bart	7/31/2024	Shift 1	End	P3	1186612
175833	Bart	7/31/2024	Shift 1	End	P2	3329164
174913	Jon	7/31/2024	Shift 2	Start	P1	1367492
174913	Jon	7/31/2024	Shift 2	Start	P3	1186612
174913	Jon	7/31/2024	Shift 2	Start	P2	3329164
175422	Peter	7/31/2024	Shift 3	Start	P1	1374583
175422	Peter	7/31/2024	Shift 3	Start	P3	1194139
175422	Peter	7/31/2024	Shift 3	Start	P2	3351105
174913	Jon	7/31/2024	Shift 2	End	P1	1374583
174913	Jon	7/31/2024	Shift 2	End	P3	1194139
174913	Jon	7/31/2024	Shift 2	End	P2	3351105
175422	Peter	7/31/2024	Shift 3	End	P1	1423881
175422	Peter	7/31/2024	Shift 3	End	P3	1234736
175422	Peter	7/31/2024	Shift 3	End	P2	3351105

amitchandak VijayPerepa

Need Help

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

Difference in Meter Reading Calculation based on dates(Updated).pbix46 KB

8 Replies

Greg_Deckler

Community Champion

1 year ago

harshagraj I don't see a Shift 3 End on 7/29/2024. So are you supposed to use the Start for Shift 3 when End is selected? A bit confused.

"If I select the End then considering July 29, 2024 it should calculate differnce as Shift3(July 29,2024)-Shift1(July 30,2024) at Shift1 (July 30,2024)"

harshagraj

Post Partisan

1 year ago

Hello Greg_Deckler thanks a lot for the reply. Actually this is a sample dataset. Technically speaking Shift1 end will be as same as Shift 2 Start and so on. I am correcting the data.

User ID	User Name	ReadingCaptureOn	Shift Name	ShiftStartOrEnd	Input	Value
174913	Jon	7/28/2024	Shift 3	Start	P1	2244567
174913	Jon	7/28/2024	Shift 3	Start	P3	2344567
174913	Jon	7/28/2024	Shift 3	Start	P2	1962234
175833	Bart	7/29/2024	Shift 1	Start	P1	2943326
175833	Bart	7/29/2024	Shift 1	Start	P3	2989733
175833	Bart	7/29/2024	Shift 1	Start	P2	3042042
175833	Bart	7/29/2024	Shift 2	End	P1	2281906
175833	Bart	7/29/2024	Shift 2	End	P3	2823592
175833	Bart	7/29/2024	Shift 2	End	P2	2969746
174913	Jon	7/29/2024	Shift 2	Start	P1	2782375
174913	Jon	7/29/2024	Shift 2	Start	P3	2982522
174913	Jon	7/29/2024	Shift 2	Start	P2	3020714
174913	Jon	7/29/2024	Shift 2	End	P1	2802801
174913	Jon	7/29/2024	Shift 2	End	P3	2605439
174913	Jon	7/29/2024	Shift 2	End	P2	2906316
174557	Wan	7/29/2024	Shift 2	Start	P1	2632718
174557	Wan	7/29/2024	Shift 2	Start	P3	2272392
174557	Wan	7/29/2024	Shift 2	Start	P2	2941521
174557	Wan	7/29/2024	Shift 3	Start	P1	2683621
174557	Wan	7/29/2024	Shift 3	Start	P3	2982543
174557	Wan	7/29/2024	Shift 3	Start	P2	2894587
174557	Wan	7/30/2024	Shift 3	End	P1	1335543
174557	Wan	7/30/2024	Shift 3	End	P3	1160300
174557	Wan	7/30/2024	Shift 3	End	P2	3326923
175833	Bart	7/30/2024	Shift 1	Start	P1	1206540
175833	Bart	7/30/2024	Shift 1	Start	P3	1050958
175833	Bart	7/30/2024	Shift 1	Start	P2	3326974
175833	Bart	7/30/2024	Shift 1	End	P1	1242837
175833	Bart	7/30/2024	Shift 1	End	P3	1081205
175833	Bart	7/30/2024	Shift 1	End	P2	3326924
174913	Jon	7/30/2024	Shift 2	Start	P1	1242837
174913	Jon	7/30/2024	Shift 2	Start	P3	1081205
174913	Jon	7/30/2024	Shift 2	Start	P2	3326924
174557	Wan	7/30/2024	Shift 3	Start	P1	1282022
174557	Wan	7/30/2024	Shift 3	Start	P3	1115002
174557	Wan	7/30/2024	Shift 3	Start	P2	3326924
174913	Jon	7/30/2024	Shift 3	End	P1	1282022
174913	Jon	7/30/2024	Shift 3	End	P3	1115002
174913	Jon	7/30/2024	Shift 3	End	P2	3326924
174913	Jon	7/30/2024	Shift 2	End	P1	1282022
174913	Jon	7/30/2024	Shift 2	End	P3	1115002
174913	Jon	7/30/2024	Shift 2	End	P2	3326924
175833	Bart	7/31/2024	Shift 1	Start	P1	1335543
175833	Bart	7/31/2024	Shift 1	Start	P3	1160300
175833	Bart	7/31/2024	Shift 1	Start	P2	3326924
175833	Bart	7/31/2024	Shift 1	End	P1	1367492
175833	Bart	7/31/2024	Shift 1	End	P3	1186612
175833	Bart	7/31/2024	Shift 1	End	P2	3329164
174913	Jon	7/31/2024	Shift 2	Start	P1	1367492
174913	Jon	7/31/2024	Shift 2	Start	P3	1186612
174913	Jon	7/31/2024	Shift 2	Start	P2	3329164
175422	Peter	7/31/2024	Shift 3	Start	P1	1374583
175422	Peter	7/31/2024	Shift 3	Start	P3	1194139
175422	Peter	7/31/2024	Shift 3	Start	P2	3351105
174913	Jon	7/31/2024	Shift 2	End	P1	1374583
174913	Jon	7/31/2024	Shift 2	End	P3	1194139
174913	Jon	7/31/2024	Shift 2	End	P2	3351105
175422	Peter	7/31/2024	Shift 3	End	P1	1423881
175422	Peter	7/31/2024	Shift 3	End	P3	1234736
175422	Peter	7/31/2024	Shift 3	End	P2	3351105
175833	Bart	7/29/2024	Shift 1	End	P1	2420065
175833	Bart	7/29/2024	Shift 1	End	P3	2734853
175833	Bart	7/29/2024	Shift 1	End	P2	2502956
174913	Jon	7/29/2024	Shift 1	End	P1	2298965
174913	Jon	7/29/2024	Shift 1	End	P3	2584894
174913	Jon	7/29/2024	Shift 1	End	P2	2713852
175833	Bart	7/29/2024	Shift 3	End	P1	2659880
175833	Bart	7/29/2024	Shift 3	End	P3	2595066
175833	Bart	7/29/2024	Shift 3	End	P2	2302885
174913	Jon	7/29/2024	Shift 3	End	P1	2853893
174913	Jon	7/29/2024	Shift 3	End	P3	2771344
174913	Jon	7/29/2024	Shift 3	End	P2	2429925

Anonymous

Not applicable

1 year ago

Hi harshagraj ,

I created a sample pbix file(see the attachment), 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" )
    )
VAR _ss2 =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[Shift Name] = "Shift 2" )
    )
VAR _ss3 =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER ( 'Table', 'Table'[Shift Name] = "Shift 3" )
    )
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

Difference in Meter Reading Calculation based on dates.pbix46 KB

Forum Discussion

Difference in Meter Reading Calculation based on dates

8 Replies

Related content

Dynamic Calculation Different Period

Different Calculations

Power BI read aloud

Calculating Differences

Calculate daily readings from multiple equipments